ORDER BY Clause
ORDER BY
Clause
Learning Outcomes
- Learn how to order a result set in ascending order.
- Learn how to order a result set in descending order.
- Learn how to order a result set in a structured order.
Lesson Materials
As explained in the SELECT
statement page, the ORDER BY
clause changes how rows are returned in a set.
ORDER BY
Clause in Ascending Order
The following query builds on the example in the HAVING
clause by adding an ORDER BY
clause. The ORDER BY
clause below filters the return set in ascending alphabetic order.
1 2 3 4 5 6 7 8 | COL brand FORMAT A30 HEADING "Brand" COL avg_per_ounce FORMAT A10 HEADING "Average|Cost|Ounces" SELECT brand , LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce FROM cereal GROUP BY brand HAVING AVG(suggested_retail/ounces) > .22 ORDER BY brand; |
It returns the following ordered rows:
Average Cost Brand Ounces ------------------------------ ---------- Apple Toast Crunch $0.27 Blueberry Toast Crunch $0.27 Chocolate Cheerios $0.23 Cinnamon Vanilla Lucky Charm $0.24 Cocoa Pebbles $0.31 Cocoa Puffs $0.31 Lucky Charm $0.24 Strawberry Toast Crunch $0.27 Trix $0.24 |
The next query builds on the prior ascending order example. The SELECT
-list returns the manufacturer
and brand
columns, and it orders them by the manufacturer
column and then the brand
column. It also changes the nature of the HAVING
evaluation. The query now checks for averages less than twenty cents.
1 2 3 4 5 6 7 8 9 10 11 | COL manufacturer FORMAT A30 HEADING "Manufacturer" COL brand FORMAT A30 HEADING "Brand" COL avg_per_ounce FORMAT A10 HEADING "Average|Cost|Ounces" SELECT manufacturer , brand , LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce FROM cereal GROUP BY manufacturer , brand HAVING AVG(suggested_retail/ounces) < .20 ORDER BY 1, 2; |
It returns the following ordered rows:
Average Cost Manufacturer Brand Ounces -------------------- ------------------------------ ---------- General Mills Cinnamon Toast Crunch $0.18 Kellogg Apple Jacks $0.16 Kellogg Corn Flakes $0.16 Kellogg Frosted Flakes $0.16 Kellogg Raisin Brand $0.13 Kellogg Raisin Brand Crunch $0.13 Kellogg Special K Original $0.20 Kellogg Special K Red Berries $0.20 Quaker Cinnamon Life $0.18 Quaker Life $0.18 |
ORDER BY
Clause in Descending Order
The following query builds on the example in the HAVING
clause by adding an ORDER BY
clause. The ORDER BY
clause below filters the return set in ascending alphabetic order.
1 2 3 4 5 6 7 8 | COL brand FORMAT A30 HEADING "Brand" COL avg_per_ounce FORMAT A10 HEADING "Average|Cost|Ounces" SELECT brand , LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce FROM cereal GROUP BY brand HAVING AVG(suggested_retail/ounces) > .22 ORDER BY brand DESC; |
It returns the following ordered rows:
Average Cost Brand Ounces ------------------------------ ---------- Trix $0.24 Strawberry Toast Crunch $0.27 Lucky Charm $0.24 Cocoa Puffs $0.31 Cocoa Pebbles $0.31 Cinnamon Vanilla Lucky Charm $0.24 Chocolate Cheerios $0.23 Blueberry Toast Crunch $0.27 Apple Toast Crunch $0.27 |
The next query builds on the prior descending order example. It also changes the nature of the HAVING
evaluation. The query now checks for averages less than twenty cents. The SELECT
-list returns the manufacturer
and brand
columns, and it orders them by:
- The
manufacturer
column in descending order, and - The
brand
column within the scope of themanufacturer
column in ascending order.
It also changes the nature of the HAVING
evaluation. The query now checks for averages less than twenty cents.
1 2 3 4 5 6 7 8 9 10 11 | COL manufacturer FORMAT A30 HEADING "Manufacturer" COL brand FORMAT A30 HEADING "Brand" COL avg_per_ounce FORMAT A10 HEADING "Average|Cost|Ounces" SELECT manufacturer , brand , LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce FROM cereal GROUP BY manufacturer , brand HAVING AVG(suggested_retail/ounces) < .20 ORDER BY 1 DESC, 2; |
It returns the following ordered rows:
Average Cost Manufacturer Brand Ounces -------------------- ------------------------------ ---------- Quaker Cinnamon Life $0.18 Quaker Life $0.18 Kellogg Apple Jacks $0.16 Kellogg Corn Flakes $0.16 Kellogg Frosted Flakes $0.16 Kellogg Raisin Brand $0.13 Kellogg Raisin Brand Crunch $0.13 Kellogg Special K Original $0.20 Kellogg Special K Red Berries $0.20 General Mills Cinnamon Toast Crunch $0.18 |
ORDER BY
Clause in Structured Order
The following query uses a CASE
operator, which allows you to assign values for the sort operation.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | COL manufacturer FORMAT A30 HEADING "Manufacturer" COL brand FORMAT A30 HEADING "Brand" COL avg_per_ounce FORMAT A10 HEADING "Average|Cost|Ounces" SELECT manufacturer , brand , LPAD(TO_CHAR(AVG(suggested_retail/ounces),'$0.00'),10,' ') AS avg_per_ounce FROM cereal GROUP BY manufacturer , brand HAVING AVG(suggested_retail/ounces) < .20 ORDER BY CASE WHEN manufacturer = 'Quaker' THEN 1 WHEN manufacturer = 'Kellogg' THEN 2 WHEN manufacturer = 'General Mills' THEN 3 END , brand; |
It returns the following ordered rows:
Average Cost Manufacturer Brand Ounces -------------------- ------------------------------ ---------- Quaker Cinnamon Life $0.18 Quaker Life $0.18 Kellogg Apple Jacks $0.16 Kellogg Corn Flakes $0.16 Kellogg Frosted Flakes $0.16 Kellogg Raisin Brand $0.13 Kellogg Raisin Brand Crunch $0.13 Kellogg Special K Original $0.20 Kellogg Special K Red Berries $0.20 General Mills Cinnamon Toast Crunch $0.18 |
The ORDER BY
clause is very powerful and allows you to sort in ascending, descending, or structured order.