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]
