SQL - Order By

ORDER BY is the SQL command used to sort rows as they are returned from a SELECT query. SQL order by command may be added to the end of any select query and it requires at least one table column to be specified in order for SQL to sort the results.

SQL Order by query:

USE mydatabase;

SELECT *
FROM orders
WHERE customer = 'Tizag'
ORDER BY day_of_order;

Executing this query should offer a list of orders made by Tizag and you may noticed that the result set has now been sorted (low to high) according to the date value. In other words, the oldest order to the newest order.

SQL Results:

id	customer	day_of_order	product	quantity
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3
6	Tia	2008-07-25 00:00:00.000	HP Printer	2
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1

SQL - Ascending Descending

The default sort order for ORDER BY is an ascending list, [a - z] for characters or [0 - 9] for numbers. As an alternative to the default sorting for our results, which is ASCENDING (ASC), we can instead tell SQL to order the table columns in a DESCENDING (DESC) fashion [z-a].

SQL Order by Descending:

USE mydatabase;

SELECT *
FROM orders
WHERE customer = 'Tizag'
ORDER BY day_of_order DESC

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3
6	Tia	2008-07-25 00:00:00.000	HP Printer	2

If you compare these results to the results above, you should notice that we've pulled the same information but it is now arranged in a reverse (descending) order.

SQL - Sorting on Multiple Columns

Results may be sorted on more than one column by listing multiple column names in the ORDER BY clause, similar to how we would list column names in each SELECT statement.

SQL Order by Multiple columns:

USE mydatabase;

SELECT *
FROM orders
ORDER BY customer, day_of_order;

This query should alphabetize by customer, grouping together orders made by the same customer and then by the purchase date. SQL sorts according to how the column names are listed in the ORDER BY clause.

SQL Results:

id	customer	day_of_order	product	quantity
3	A+Maintenance	2008-08-16 00:00:00.000	Hanging Files	12
4	Gerald Garner	2008-08-15 00:00:00.000	19" LCD Screen	3
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3
6	Tia	2008-07-25 00:00:00.000	HP Printer	2
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1

Posted in: SQL