Database Tutorial

Course Tutorial Site

Site Admin

F – ORDER BY Clause

without comments

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 michaelmclaughlin

January 11th, 2018 at 12:16 am

Posted in