CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle ORDER BY Clause
Version 11.1
 
General
Note: The ORDER BY clause is used by a SELECT statement
 
By Column Name

Single Column Ascending
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name>;
SELECT *
FROM table_name;

SELECT table_name
FROM all_tables
ORDER BY table_name;

Single Column Descending
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name> DESC
SELECT table_name
FROM all_tables
ORDER BY table_name DESC;

Order By Multiple Columns
SELECT <column_name>, <column_name
FROM <table_name>
ORDER BY <column_name>, <column_name>
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY tablespace_name, table_name;

Order By Multiple Columns With Mixed Ascending And Descending Orders
SELECT <column_name>, column_name
FROM <table_name>
ORDER BY <column_name>, <column_name> DESC
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY tablespace_name DESC, table_name;
 
By Column Position

Single Column Ascending
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number>
SELECT table_name
FROM all_tables
ORDER BY 1;

Single Column Descending
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number> DESC
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2 DESC;

Order By Multiple Columns
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number>, <position_number>
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2, 1;

Order By Multiple Columns With Mixed Ascending And Descending Orders
SELECT <column_name>
FROM <table_name>
ORDER BY <position_number> ASC, <position_number> DESC
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 1 ASC, 2 DESC;
 
Order Nulls

Nulls First
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name_or_position_number> NULLS FIRST;
SELECT tablespace_name
FROM all_tables
ORDER BY tablespace_name NULLS FIRST;

Nulls Last
SELECT <column_name>
FROM <table_name>
ORDER BY <column_name_or_position_number> NULLS LAST;
SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2, 1 NULLS LAST;
 
The Cost of Ordering

ORDER BY Overhead
CREATE TABLE t AS
SELECT * FROM all_objs;


EXPLAIN PLAN FOR
SELECT * FROM t;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT * FROM t
ORDER BY 1;

SELECT * FROM TABLE(dbms_xplan.display);
 
Non-Default Ordering

Ordering With A Decode
spool c: emp ecompile.sql

SELECT 'ALTER ' || object_type || ' '|| object_name ||' COMPILE;'
FROM user_objects
WHERE object_type IN ('FUNCTION', 'PACKAGE', 'PROCEDURE', 'TRIGGER', 'VIEW')
ORDER BY DECODE(object_type, 'VIEW','A', 'FUNCTION','B',
'PROCEDURE', 'C', 'PACKAGE','D', 'Z');

spool off
 
ORDER BY Demo

Ordering Takes Place After Selection
CREATE TABLE ob_demo (
datecol  DATE,
numbcol  NUMBER(3));

BEGIN
  FOR i IN 1 .. 10
  LOOP
    INSERT INTO ob_demo
    (datecol, numbcol)
    VALUES
    (TRUNC(SYSDATE + i), MOD(i*11,13));
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM ob_demo;

-- Invalid
SELECT datecol, numbcol
FROM ob_demo
WHERE ROWNUM = 1
ORDER BY datecol;

-- This does not work because rownum is
-- evaluated before the order by.


-- Valid
SELECT datecol, numbcol
FROM (
  SELECT row_number() OVER (ORDER BY datecol DESC) r,
  datecol, numbcol
  FROM ob_demo
)
WHERE r=1;

-- The inner select orders datecol descending and assigns a
-- a value to 'r'. This value starts with 1 and is
-- incremented by 1 for each row. The outer select then
-- selects the row where r = 1.

SELECT row_number() OVER (ORDER BY datecol DESC) r, datecol, numbcol
FROM ob_demo;
 
Related Topics
Explain Plan
Select
String Functions - NLS_SORT
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [231 users online]    © 2010 psoug.org