Error 1250 Table from one of the SELECT's cannot be used in global ORDER clause

[Dave Torr]

I am getting the above error (the number is wrong - manual says this is 
1249) on a statement of the form

(SELECT species_table.species_id,count(*) as c FROM species_table INNER JOIN 
checklist_table USING (species_id,subspecies_id,name_id) GROUP BY 
species_id,source HAVING c=1)
UNION
(SELECT species_table.species_id,count(*) as c FROM species_table INNER JOIN 
checklist_table USING (species_id,subspecies_id,name_id) GROUP BY species_id 
HAVING c>1)
ORDER BY species_table.species_id ASC  LIMIT 0,31

(And before you point out that this does not actually achieve much - the 
original staement is more complex and I have simplified it for this mail!)

THe simple manual example
(SELECT a FROM tbl_name INNER JOIN xx WHERE conditons)
UNION
(SELECT a FROM tbl_name INNER JOIN xx WHERE conditions)
ORDER BY a
works fine
And I get the above error message. Any clues as to what is causing it. If I 
remove the ORDER BY the statement runs correctly - so there is no error in 
the SELECT statements.

This is on the latest 4.1 alpha - so is there an error in my SQL statement 
or is this a bug??


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]