SQL ORDER-BY Clause

When we want to sort or arrange result set with ascending or descending order, we can use Order By keyword in sql query. By default, any sql statement return result set in ascending order, if you want to change that order, you may need to use DESC keyword to do that.

Let’s understand each with syntax and examples.

MS SQL

Syntax:

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
ORDER BY COLUMN1 ASC | DESC

Example:

Original Data in table called “Employee”


Id Employee Code First Name Last name Gender Birth Date
1 EMP_1001 John Peter Male 10-11-1982
2 EMP_1002 Pamela Wills Male 09-07-1980
3 EMP_1003 Gil Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989
5 EMP_1005 Martyn Mondry Male 02-06-1991


SELECT * FROM Employee
ORDER BY Firstname ASC

Data displayed from table “Employee” after above Sql Query executed


Id Employee Code First Name Last name Gender Birth Date
4 EMP_1004 Aidan Walls Male 05-03-1989
3 EMP_1003 Gil Parkar Female 08-09-1990
1 EMP_1001 John Peter Male 10-11-1982
5 EMP_1005 Martyn Mondry Male 02-06-1991
2 EMP_1002 Pamela Wills Male 09-07-1980


SELECT * FROM Employee
ORDER BY Firstname DESC

Data displayed from table “Employee” after above Sql Query executed


Id Employee Code First Name Last name Gender Birth Date
2 EMP_1002 Pamela Wills Male 09-07-1980
5 EMP_1005 Martyn Mondry Male 02-06-1991
1 EMP_1001 John Peter Male 10-11-1982
3 EMP_1003 Gil Parkar Female 08-09-1990
4 EMP_1004 Aidan Walls Male 05-03-1989


You may also use multiple column name for order by keyword. Below sql statement will execute and return records with sort order Gender as Descending and First Name as Ascending order.


SELECT * FROM Employee
ORDER BY Gender DESC,Firstname ASC

Data displayed from table “Employee” after above Sql Query executed


Id Employee Code First Name Last name Gender Birth Date
4 EMP_1004 Aidan Walls Male 05-03-1989
1 EMP_1001 John Peter Male 10-11-1982
5 EMP_1005 Martyn Mondry Male 02-06-1991
2 EMP_1002 Pamela Wills Male 09-07-1980
3 EMP_1003 Gil Parkar Female 08-09-1990