# MacLochlainns Weblog

Michael McLaughlin's Technical Blog

## 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 `manufacturer` column in descending order, and
• The `brand` column within the scope of the `manufacturer` 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.

Written by maclochlainn

December 25th, 2017 at 1:13 am