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
List-Archive: http://lists.mysql.com/mysql/167277
Message-Id: <00dc01c452a4$b6ac8410$0a02a8c0@martijn>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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