ORDER BY Statement in SQL - ASC, DESC and multiple cols

In SQL, the ORDER BY statement is used to sort the result set of a query in ascending or descending order based on one or more columns. It is a crucial component for organizing and presenting data in a readable and meaningful manner.

Syntax

The basic syntax of the ORDER BY statement is as follows:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

• SELECT: Specifies the columns to be retrieved.
• FROM: Specifies the table from which to retrieve the data.
• ORDER BY: Specifies the column(s) by which the result set should be sorted.
• ASC: Ascending order (default if not specified).
• DESC: Descending order.

Examples

Let’s consider a simple table named “Employees” with the following structure:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000
2	Jane	Smith	60000
3	Bob	Johnson	55000

Example 1: Sorting by EmployeeID in Ascending Order

SELECT * FROM Employees
ORDER BY EmployeeID;

Output:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000
2	Jane	Smith	60000
3	Bob	Johnson	55000

Example 2: Sorting by Salary in Descending Order

SELECT * FROM Employees
ORDER BY Salary DESC;

Output:

EmployeeID	FirstName	LastName	Salary
2	Jane	Smith	60000
3	Bob	Johnson	55000
1	John	Doe	50000

Example 3: Sorting by FirstName in Ascending Order

SQL Query:

SELECT * FROM Employees
ORDER BY FirstName;

Output:

EmployeeID	FirstName	LastName	Salary
3	Bob	Johnson	55000
2	Jane	Smith	60000
1	John	Doe	50000

Example 4: Sorting by LastName in Descending Order

SQL Query:

SELECT * FROM Employees
ORDER BY LastName DESC;

Output:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000
3	Bob	Johnson	55000
2	Jane	Smith	60000

Example 5: Sorting by Salary and then by FirstName

SQL Query:

SELECT * FROM Employees
ORDER BY Salary, FirstName;

Output:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000
3	Bob	Johnson	55000
2	Jane	Smith	60000

Example 6: Sorting by Salary in Descending Order and by FirstName in Ascending Order

SQL Query:

SELECT * FROM Employees
ORDER BY Salary DESC, FirstName;

Output:

EmployeeID	FirstName	LastName	Salary
2	Jane	Smith	60000
3	Bob	Johnson	55000
1	John	Doe	50000

Example 7: Sorting by LastName in Ascending Order, then by FirstName in Descending Order

SQL Query:

SELECT * FROM Employees
ORDER BY LastName, FirstName DESC;

Output:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000
3	Bob	Johnson	55000
2	Jane	Smith	60000

Example 8: Sorting by Salary in Descending Order, excluding Employees with Salary Less Than 55000

SQL Query:

SELECT * FROM Employees
WHERE Salary >= 55000
ORDER BY Salary DESC;

Output:

EmployeeID	FirstName	LastName	Salary
2	Jane	Smith	60000
3	Bob	Johnson	55000

ORDER BY multiple columns

When we sort on multiple columns, SQL first sorts the result set based on the first column specified in the ORDER BY clause. If there are ties (i.e., values in the first column are the same), it then sorts based on the second column, and so on.

Example 9: Sorting by LastName in Ascending Order, then by Salary in Descending Order

SQL Query:

SELECT * FROM Employees
ORDER BY LastName, Salary DESC;

Output:

EmployeeID	FirstName	LastName	Salary
1	John	Doe	50000
3	Bob	Johnson	55000
2	Jane	Smith	60000