ORDER BY clause in SQL enables us to sort results of the query by specific field (column) or more columns. There are 2 sorting options:
- Ascending order (ASC) – sorts records from lowest to highest in case of numbers and from A to Z in case of text strings
- Descending order (DESC) – sorts records from highest to lowest in case of numbers and from Z to A in case of text strings
If we do not state how we want the data to be sorted, default setting will be selected. Meaning ASC order will be in effect.
ORDER BY Clause in SQL Script
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
Syntax
SELECT [Column 1], [Column 2], [Column 3]
FROM Table
ORDER BY [Column 1] ASC/DESC, [Column 2] ASC/DESC
This clause belongs to the end of SQL script. We can sort the data according to one or multiple columns. In that case, we filter according to [Column 1] with priority in ascending order and according to [Column 2] secondarily in descending order.
Example
Table which we will work with is shown below. It consists of 18484 records.
If we follow an example in article SQL WHERE, not only that we used limiting requirement on the table but we will also sort the result using ORDER BY.
This time we need from the table:
- to choose all customers who have [CustomerKey] between 10000 and 15000
- sort the result by the surnames [Last Name] in alphabetical order and in the same time sort the dates of birth [BirthDate] in descending order
SELECT
[CustomerKey]
,[FirstName]
,[LastName]
,[BirthDate]
,[Gender]
FROM [AdventureWorksDW2012].[dbo].[DimCustomer]
WHERE [CustomerKey] BETWEEN 10000 AND 15000
ORDER BY
[LastName] ASC
,[BirthDate] DESC
As a result, we get a table with the result sorted firstly by surname from A to Z and if there is match in surnames (e. g. Mr. Adams in the picture) we sort the matched results by the date of birth in descending order. I add picture with explanation of how the data is sorted.
We would obviously get completely different result if we turned the order in ORDER BY clause the other way around. That is because we would sort firstly by date of birth [BirthDate] and only after that we would be sorting by Surname [LastName]. Take a look at how it would look like:
