You can use ordinal number for ORDER BY clause.
ORDER BY applies to the final, combined union's result set.
No, you cannot use ordinal number for the projection part of the SELECT
statement. What you could do is define column aliases within your individual
selects, or even better, define a column definition list for a common table
expression and then use it in the final select. For example, assuming there
are four fields returned:

With CTE1 (f1,f2,f3,f4) as
(
select * from fileA where ....
UNION ALL
select * from fileB where ....
)
SELECT * FROM CTE1
ORDER BY 1,2,3,4

Order By can be within a CTE or outside. You can use ordinal number or
column aliases (f1,f2,f3,f4).

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL Question - Merging files or result sets?

Would

ORDER by x, y, z

work? Where x, y and z are numbers representing the position of the fields
in the result set that I want to sort by?

I just found an example online that did that. Can I use numbers for SELECT
clause too? That would mean I'd only need one select clause, which would be
great.

Bradley V. Stone


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].