SQL ORDER BY – Sorting Data in SQL Table in Descending or Ascending Order

Last modified date:

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

Hint: 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

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. 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.

Dimcustomer-pred-WHERE-klauzuli

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.

SQL-ORDER-BY example

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:

SQL-ORDER-BY-different example-en2

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Basics SQL Clauses

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.