List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:June 15 2004 6:48am
Subject:Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER clause
View as plain text  
Hi Dave,


> 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

Difference here: you're not using
ORDER BY tbl_name.a

> 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.

So, try:

ORDER BY species_id ASC  LIMIT 0,31

or

ORDER BY 1 ASC  LIMIT 0,31

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

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