Sorting in UNION ALL

shri7 · #1 (**permalink**) 11-08-07, 19:49

Hi

I have following query

SELECT 'ALL'
UNION ALL
SELECT DISTINCT Emp_Name

FROM

EMP
How do I sort the result ?
Thanks

r937 · #2 (**permalink**) 11-08-07, 20:28

by adding an ORDER BY clause

if you don't give the column an alias name in the first subselect, you can use the ordinal column number --

Code:

SELECT 'ALL' 
UNION ALL 
SELECT DISTINCT Emp_Name 
  FROM EMP 
ORDER
    BY 1

alternatively, if you reverse the subselects, you can use the column name --

Code:

SELECT DISTINCT Emp_Name 
  FROM EMP 
UNION ALL 
SELECT 'ALL' 
ORDER
    BY Emp_Name

shri7 · #3 (**permalink**) 11-08-07, 21:23

Thanks for the quick reply. It worked