| 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; |
