Reference alias name in order by clause : Order By « Query Select « Oracle PL/SQL Tutorial

SQL>
SQL>
SQL> CREATE TABLE employees (
  2    au_id    CHAR(3)     NOT NULL,
  3    au_fname VARCHAR(15) NOT NULL,
  4    au_lname VARCHAR(15) NOT NULL,
  5    phone    VARCHAR(12) NULL    ,
  6    address  VARCHAR(20) NULL    ,
  7    city     VARCHAR(15) NULL    ,
  8    state    CHAR(2)     NULL    ,
  9    zip      CHAR(5)     NULL
 10  );

Table created.

SQL>
SQL> INSERT INTO employees VALUES('A01','S','B','111-111-1111','75 St','Boston','NY','11111');

1 row created.

SQL> INSERT INTO employees VALUES('A02','W','H','222-222-2222','2922 Rd','Boston','CO','22222');

1 row created.

SQL> INSERT INTO employees VALUES('A03','H','H','333-333-3333','3800 Ave, #14F','San Francisco','CA','33333');

1 row created.

SQL> INSERT INTO employees VALUES('A04','K','H','444-444-4444','3800 Ave, #14F','San Francisco','CA','44444');

1 row created.

SQL> INSERT INTO employees VALUES('A05','C','K','555-555-5555','114 St','New York','NY','55555');

1 row created.

SQL> INSERT INTO employees VALUES('A06',' ','K','666-666-666','390 Mall','Palo Alto','CA','66666');

1 row created.

SQL> INSERT INTO employees VALUES('A07','P','O','777-777-7777','1442 St','Sarasota','FL','77777');

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT au_fname AS "First name",
  2         au_lname AS "Last name",
  3         state
  4    FROM employees
  5    ORDER BY state        ASC,
  6             "Last name"  ASC,
  7             "First name" ASC;

First name      Last name       ST
--------------- --------------- --
H               H               CA
K               H               CA
                K               CA
W               H               CO
P               O               FL
S               B               NY
C               K               NY

7 rows selected.

SQL>
SQL>
SQL> drop table employees;

Table dropped.

2.4.Order By
	2.4.1.	Sorting Rows Using the ORDER BY Clause
	2.4.2.	Sort last name ascending
	2.4.3.	Sort last name descending
	2.4.4.	Sort one column ascending and another column descending
	2.4.5.	Sort by index ascending and descending
	2.4.6.	Reference alias name in order by clause
	2.4.7.	Order by a calculated column
	2.4.8.	Sort the rows by descending order with DESC appended to order by clause
	2.4.9.	Sort two columns with different ordering
	2.4.10.	Use a column position number in the ORDER BY clause
	2.4.11.	A SELECT with Ordering
	2.4.12.	Order the table on the employee's original salary (orig_salary)
	2.4.13.	Order by two columns
	2.4.14.	Add aggregate function in order by clause
	2.4.15.	Order by username
	2.4.16.	Combine order by clause with case statement
	2.4.17.	Order Your Email