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
