[All]
Ordering the results of a UNION
Abstract: Ordering the results of a UNION
Problem:
Using an ORDER BY clause in a Select statement that uses a UNION clause will produce an
error : '-Invalid command -invalid ORDER BY clause'. How do you arrange the results of a UNION?
Solution:
The answer is use field numbers rather than field names as arguments in the ORDER BY clause.
Given the following table structures, here is the right way and the wrong way of ordering
a UNION result:
Create table FRIENDS ( First_name Char(15), Last_Name Char(25), Birth Date);
Create table ENEMIES( First_name Char(15), Last_Name Char(25), Birth Date);
The right way:
Select First_name, Last_name from Friends
UNION
Select First_name, Last_name from Enemies
ORDER BY 2, 1
The wrong way, which yields an error:
Select First_name, Last_name from Friends
UNION
Select First_name, Last_name from Enemies
ORDER BY Last_name, First_name
Here is the error you will see if you run the above query with InterBase 5.0:
Dynamic SQL Error
-SQL error code = -104
-Invalid command
-invalid ORDER BY clause
Note: The numbers in the first query's ORDER BY clause refer to columns in the result
set, not the source tables.
