Re: How to identify a column in order by clause

doichi wrote:
> I would go for the alias if had to choose between alias and tbl.col
> notation, but there is better alternative:
>
> Almost sure that Oracle will not accept tbl.col notation in ORDER
> clause if alias is present (Sorry just uninstalled Oracle and can not
> test it).
> MS SQL is more generous and allows any permutation (tbl.aslias,
> tbl.col, alias...).
> MS Jet DB engine howerver does not allow aliases in ORDER BY clause.
> MySQL and Postgre have no problems with aliases in ORDER BY clause.
>
> All of the reviewed db engines work fine with 1, 2, n (positional
> notation) in the ORDER BY clause. So if you want "write once run
> anywhere" I would go with positional ORDER BY notation like this:
> SELECT [emp.]empno [AS alias], [emp.]ename [AS alias], [emp.]sal [AS
> alias]
> FROM emp [AS alias]
> ORDER BY 1, 2
>
> BR,
> Doich
>
> todd_run wrote:
>

>>The question I have is:  In a select statement that uses column
>>aliases, should the order by clause include the column name or the
>>column alias?

Don't be so sure.

   1 select table_name, column_name AS COLNAME    2 from user_tab_columns
   3* where table_name = 'SERVERS'
SQL> /

TABLE_NAME                     COLNAME
------------------------------ -----------------------
SERVERS                        SRVR_ID
SERVERS                        NETWORK_ID
SERVERS                        STATUS
SERVERS                        LATITUDE
SERVERS                        LONGITUDE
SERVERS                        NETADDRESS

6 rows selected.

SQL> ed
Wrote file afiedt.buf

   1 select table_name, column_name AS COLNAME    2 from user_tab_columns
   3 where table_name = 'SERVERS'
   4* ORDER BY column_name
SQL> /

TABLE_NAME                     COLNAME
------------------------------ -----------------------
SERVERS                        LATITUDE
SERVERS                        LONGITUDE
SERVERS                        NETADDRESS
SERVERS                        NETWORK_ID
SERVERS                        SRVR_ID
SERVERS                        STATUS

6 rows selected.

SQL> ed
Wrote file afiedt.buf

   1 select table_name, column_name AS COLNAME    2 from user_tab_columns
   3 where table_name = 'SERVERS'
   4* ORDER BY colname
SQL> /

TABLE_NAME                     COLNAME
------------------------------ -----------------------
SERVERS                        LATITUDE
SERVERS                        LONGITUDE
SERVERS                        NETADDRESS
SERVERS                        NETWORK_ID
SERVERS                        SRVR_ID
SERVERS                        STATUS

6 rows selected.

SQL> The best solution is, as always, positional notation

SQL> ed
Wrote file afiedt.buf

   1 select table_name, column_name AS COLNAME    2 from user_tab_columns
   3 where table_name = 'SERVERS'
   4* ORDER BY 2
SQL> /

TABLE_NAME                     COLNAME
------------------------------ ---------------
SERVERS                        LATITUDE
SERVERS                        LONGITUDE
SERVERS                        NETADDRESS
SERVERS                        NETWORK_ID
SERVERS                        SRVR_ID
SERVERS                        STATUS

6 rows selected.

SQL> then it doesn't matter what you call it.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)