6 – 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
manufacturercolumn in descending order, and - The
brandcolumn within the scope of themanufacturercolumn 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.
Subscribe via RSS