ORDER BY clause

ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by.

ORDER BY clause syntax:

SELECT column1, SUM(column2) 
FROM "list-of-tables" 
ORDER BY "column-list" [ASC | DESC];

[ ] = optional

This statement will select the employee_id, dept, name, age, and salary from the employee_info table where the dept equals ‘Sales’ and will list the results in Ascending (default) order based on their Salary.

ASC = Ascending Order - default

DESC = Descending Order

 
For example:

SELECT employee_id, dept, name, age, salary 
FROM employee_info 
WHERE dept = 'Sales' 
ORDER BY salary;

If you would like to order based on multiple columns, you must separate the columns with commas. For example:

SELECT employee_id, dept, name, age, salary 
FROM employee_info 
WHERE dept = 'Sales' 
ORDER BY salary, age DESC;

Review Exercises

1. Select the lastname, firstname, and city for all customers in the customers table. Display the results in Ascending Order based on the lastname.

   Show Answer

   SELECT lastname, firstname, city 
   FROM customers 
   ORDER BY lastname;

2. Same thing as exercise #1, but display the results in Descending order.

   Show Answer

   SELECT lastname, firstname, city 
   FROM customers 
   ORDER BY lastname DESC;

3. Select the item and price for all of the items in the items_ordered table that the price is greater than 10.00. Display the results in Ascending order based on the price.

   Show Answer

   SELECT item, price 
   FROM items_ordered 
   WHERE price > 10.00 
   ORDER BY price ASC;

Results will be displayed here