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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
