SQL- Sorting Data with ORDER BY Clause

The data inserted in the tables is processed and presented in many ways as required by the user. The most common expectation from data processing is sorting data in ascending or descending order by one or more columns of the table. In this tutorial you will learn about  sorting data with ORDER BY clause in the SELECT statement. Sorting can be applied on single or multiple columns. You can sort column with all data types like number, string, character and date.

Syntax of Sorting Data with ORDER BY

ORDER BY clause is used with the SELECT statement. It is one of the most commonly used clauses. The data stored in table is unsorted since it is added in sequence by multiple INSERT statements. The stored unsorted data can be presented to the user in a more understandable and sorted manner.

The Sorting Orders used are

• Ascending Order (ASC)– Number columns values are displayed with smallest value at top and largest values at bottom. The string column values are displayed in alphabetical order just like a dictionary. The date values are displayed with oldest dates at top and latest dates towards end of displayed data.
• Descending Order(DESC)– Number columns displays largest value at top and smallest values at bottom. The string column values are displayed in reverse alphabetical order of that of a dictionary. The date values are displayed with latest dates at top and older dates towards end of displayed data.

SELECT [DISTINCT] *| column name1 alias1, column name2 alias2,…..
FROM table name
[WHERE condition]
[ORDER BY  order-column-name1 [ASC|DESC], order-column-name2[ASC|DESC]…]

• SELECT, DISTINCT, FROM, WHERE, ORDER BY are the keywords
• *|Column Name1, Column Name2,… – defines the columns whose values must be displayed in the output of SELECT query statement.
• table name is the name of the table from which you want to access data.
• Condition expression in the WHERE clause applies filter on the data being fetched from the table
• alias name is optional. It is used to change the column heading of data being displayed
• order-column-name1 [ASC|DESC] is the column name on which you want to sort data. You can add multiple columns here to nest the sorting
• By default the order is ASC. If you don’t specify the order (ASC or DESC) data will be sorted in ascending order. You have to specify the DESC keyword to sort in reverse order.

Examples of Sorting Data with ORDER BY

To understand  the examples of ORDER BY clause you have to use the following data in OrdersTbl we have been using in earlier lessons. You can see that the data in this table is not sorted on any column. So let’s begin with example

SELECT  * FROM  ordersTbl ORDER BY ORDERID;

The data is displayed after it is sorted on OrderID column in ascending order. You can see that ASC or DESC keywords are not specified. In this case default order of sorting is ASC.

SELECT  * FROM  ordersTbl ORDER BY ORDERID DESC;

The data is displayed after it is sorted on OrderID column in descending order. To display data in descending order you need to specify DESC keyword after column name.

SELECT  * FROM  ordersTbl ORDER BY SPID, ORDERID DESC;

This example uses two columns in ORDER BY Clause- SPID and ORDER ID. In this case the sorting is performed from left to right of the columns in ORDER BY clause. You can see that first the data is sorted by SPID in ascending order. Then within the first level sorting of SPID the second level sorting is done for OrderID column in descending order.

SELECT  * FROM  ordersTbl ORDER BY SPID ASC, ORDERDATE DESC, ORDERID DESC;

This example uses three columns in ORDER BY Clause- SPID, ORDERDATE and ORDER ID. In this case the sorting is performed from left to right of the columns in ORDER BY clause. You can see that first the data is sorted by SPID in ascending order. Then within the first level sorting of SPID the second level sorting is done for ORDER DATE column in descending order. Finally within the second level sorting of ORDERDATE the third level sorting is done for ORDERID column in descending order.

SELECT  * FROM  ordersTbl WHERE Custid=101 ORDER BY SPID ASC, ORDERDATE DESC,  ORDERID ASC;

This example uses three columns in ORDER BY Clause SPID, ORDERDATE and ORDERID in addition to the WHERE Clause that filters out data for CUSTID 101. This query works by first filtering the data using WHERE condition.  Then by applying the ORDER BY from left to right of the columns specified.

Points to remember

To sort data of a table remember the following things

• Sorting data of a table or set of tables do not change the order of data in table(s) used.
• WHERE clause can be added to a SELECT statement along with ORDER BY clause. The data that satisfies WHERE condition will be presented to the user after sorting.
• Sorting is done from left to right when more than one column is listed in the ORDER BY clause.
• You can add different sorting order for different columns. You can mix and match ASC and DESC with different columns in ORDER BY Clause according to your data needs.