The SQL ORDER BY statement determines the order in which records are returned by a query. You can return records in ascending or descending order by the value of any column in the table.
Ordering query results makes it easier to find the exact records you are looking for.
For example, say you wanted to know which employees had won the most employee of the month awards. You may want to order your query by that data point to display the employee who had won the most awards first.
The SQL ORDER BY statement orders the results of a query depending on your conditions. In this tutorial, we are going to discuss how to use the ORDER BY statement. We’ll talk through an example to help you master this keyword.
SQL ORDER BY
The SQL ORDER BY keyword orders the results of a query. You can order results in either ascending or descending order by a particular column or set of columns.
Here is the syntax for an ORDER BY query:
SELECT name FROM employees ORDER BY name, joined ASC;
After the SQL SELECT statement, we specify the name of the column we want to retrieve. We then state we want to retrieve data from the employees table.
The ORDER BY statement orders our query by the name and joined columns in descending order. Our database will first be ordered by names in descending order.
If there are multiple people who have the same name, the person who joined the latest will appear at the bottom of those names.
SQL orders a column in ascending order by default. You can order a column in asecnding or descending order using the SQL ORDER BY clause.
The two possible sort orders are:
- ASC: Ascending.
- DESC: Descending.
You can use a column list to order multiple columns by different orders:
SELECT name FROM employees ORDER BY name ASC, joined DESC;
This query orders our table by name in ascending order. Then, our table is ordered by joined in descending order.
ORDER BY SQL Example
Let’s run an example query to demonstrate the ORDER BY operator in action. We want to retrieve a list of all employee names and the titles for each employee. Our goal is to sort this list in alphabetical order by employee name.
To retrieve this data, we could use the following query:
SELECT name, title FROM employees ORDER BY name ASC;
Our query sorts the result set. Here is our result set in ascending order:
name | title |
Alexis | Sales Associate |
Geoff | Senior Sales Associate |
Hannah | Sales Associate |
Jonah | Vice President of Sales |
Luke | Sales Associate |
Mike | Sales Associate |
If you wanted to sort in reverse alphabetical order, you would substitute the ASC statement for DESC.
You can also sort by multiple columns. This can be useful if you have duplicate values in your table that you want to be sorted.
For example, say you want to find out who has been working for you for the shortest time, in order of their titles. You could do this using the following query:
SELECT name, title, hired_date FROM employees ORDER BY title, hired_date DESC;
Our ORDER BY statement sorts the query results. Here is the result of our query:
name | title | hired_date |
Emma | Marketing Director | 2010-03-19 |
Alexis | Sales Associate | 2014-04-01 |
Hannah | Sales Associate | 2011-09-30 |
Mike | Sales Associate | 2010-03-19 |
Luke | Sales Associate | 2009-12-03 |
Geoff | Senior Sales Associate | 2012-03-17 |
Jonah | Vice President of Sales | 2010-07-23 |
(7 rows)
As you can see, our table is sorted by both the employee’s title and date on which they were hired. Our ORDER BY statement successfully sorted the records.
The most recently hired sales associate was Alexis. Our sales associate working for the company the longest is Luke. We can also see the date on which every other employee was hired, in order of their title.
Conclusion
The SQL ORDER BY statement modifies the order in which records are returned by a query. You can order a query in ascending or descending order. The default is for a query to return data in ascending order.
Do you want to learn more about SQL? Read our How to Learn SQL guide. You’ll find top tips on how to learn SQL. We have prepared a list of learning resources in our guide that will help you build the skills you need.
About us: Career Karma is a platform designed to help job seekers find, research, and connect with job training programs to advance their careers. Learn about the CK publication.