SQL Basics
Querying Data
Filtering Data
Joining Tables
SQL Functions
Modifying Data
Defining Data
SQL ORDER BY
In this tutorial, you will learn how to sort the result set using the SQL ORDER BY clause.
When you use SELECT statement to retrieve data from a table, the rows in result set are not in any particular order. It means that DBMS can return a result set with an unspecified order.
Syntax
Now if you want your result set to be sorted in a particular order.
The basic syntax of the ORDER BY clause to sort the data returned by a query, can be given with:
SELECT column_names
FROM table_name
ORDER BY column_name ASC|DESC
- The place of ORDER BY clause is at the end of statement.
- You have two options to sort your data. You can sort the rows in ascending using ASC Keyword or in descending order using DESC.
- By default ORDER BY sort the result using ASC, which means if you dont specifiy ASC or DESC the sorting will be done by default in ascending order.
Example
To understand the ORDER BY statement in a better way, let’s look at the following customers table in our tutorial database:
| customer_id | first_name | last_name | country | score |
|---|---|---|---|---|
| 1 | Maria | Cramer | Germany | 350 |
| 2 | John | Steel | USA | 900 |
| 3 | Georg | Pipps | UK | 750 |
| 4 | Martin | Müller | Germany | 500 |
| 5 | Peter | Franken | USA | NULL |
Now, let’s check out some examples that demonstrate how it actually works.
Sorting Single Column
We have the following task be to solve using SQL statements
The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.
You can have the same the result set by skipping the ASC, because it is the default option in ORDER BY.
SELECT *
FROM customers
ORDER BY score ASC
SELECT *
FROM customers
ORDER BY score
After executing the above query, you’ll get the result set something like this:
As you can see the output contains everything the whole customers tables but the rows are sorted by score in ascending order
Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.
SELECT *
FROM customers
ORDER BY score DESC
Sorting Multiple Columns
Sometimes you could be in situation where sorting data using one column would not be enough because you might have a lot of duplicate values in your data. Then you can specify more than one column while sorting.
Now, let’s check out some examples that demonstrate how it actually works. let’S say we have the following task:
First we start sorting our data using the first column. The following SQL statement will returns all customers and order the result by the country in ascending order
SELECT *
FROM customers
ORDER BY country ASC
After executing the above query, you’ll get the result set something like this:
As you can see the result contains only the columns that we specified after SELECT.
SELECT *
FROM customers
ORDER BY country ASC, score DESC
Notice the change in position of two customers “Martin” and “Maria”.
Once you specify multiple columns after ORDER BY, the Database will sort the result by the first column, then the new ordered list will be sorted again by next column.
SELECT *
FROM customers
ORDER BY country ASC, score DESC
SELECT *
FROM customers
ORDER BY 1 ASC, 2 DESC
Sorting by Numbers
It is possible to use the position number of the column instead of using the column name like this:
SELECT *
FROM customers
ORDER BY 1 ASC, 2 DESC
It is possible to use the position number of the column instead of using the column name like this:
Avoid using numbers in ORDER BY. It is better to use the name of columns
