Datasheet

40 Chapter 1

Introducing SQL
You can use column alias names in the ORDER BY clause.
If the DISTINCT keyword is used in the SELECT clause, you can use only those columns
listed in the
SELECT clause in the ORDER BY clause. If you have used any operators on columns in
the
SELECT clause, the ORDER BY clause also should use them. Here is an example:
SELECT DISTINCT ‘Region ‘ || region_id
FROM countries
ORDER BY region_id;
ORDER BY region_id
*
ERROR at line 3:
ORA-01791: not a SELECTed expression
SELECT DISTINCT ‘Region ‘ || region_id
FROM countries
ORDER BY ‘Region ‘ || region_id;
‘REGION’||REGION_ID
-----------------------------------------------
Region 1
Region 2
Region 3
Region 4
Not only can you use the column name or column alias to sort the result set of a query,
but you can also sort the results by specifying the position of the column in the
SELECT clause.
This is useful if you have a lengthy expression in the
SELECT clause and you need the results
sorted on this value. The following example sorts the result set using positional values:
SELECT first_name, hire_date, salary, manager_id mid
FROM employees
WHERE department_id IN (110,100)
ORDER BY 4, 2, 3;
FIRST_NAME HIRE_DATE SALARY MID
-------------------- --------- ---------- ----------
Shelley 07-JUN-94 12000 101
95127c01.indd 40 2/18/09 6:37:10 AM