List:General Discussion« Previous MessageNext Message »
From:Dave Torr Date:June 15 2004 6:38am
Subject:Error 1250 Table from one of the SELECT's cannot be used in global ORDER clause
View as plain text  
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??


Thread
Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseDave Torr15 Jun
  • Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseMartijn Tonies15 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDERclauseDave Torr16 Jun
  • Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseMartijn Tonies16 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDERclauseDave Torr16 Jun
  • Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clauseMartijn Tonies16 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in globalORDER clauseSGreen16 Jun
Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDERclauseDave Torr17 Jun