List:General Discussion« Previous MessageNext Message »
From:Dave Torr Date:June 15 2004 10:23pm
Subject:Re: Error 1250 Table from one of the SELECT's cannot be used in global ORDER
clause
View as plain text  
Unfortunately species_id is a column I am using for joining so I need the 
fully qualified name both in the SELECT and the ORDER BY.

Anyway - I have just installed 40.17 on an old PC and found this statement 
works on 4.0.17, so it is clearly a "feature" of 4.1.2 and I will report 
it....

Thanks anyway


>From: "Martijn Tonies" <m.tonies@stripped>
>To: <mysql@stripped>
>Subject: Re: Error 1250 Table from one of the SELECT's cannot be used in 
>global ORDER clause
>Date: Tue, 15 Jun 2004 08:48:06 +0200
>
>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
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>


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