ORDER BY Clause in SQL

Introduction to ORDER BY Clause in SQL

ORDER BY clause in SQL sorts the records of the column of a table in the SQL database. It helps us sort the column in both ascending and descending order. The ASC keyword helps us sort in ascending order while the DESC sorts in descending order. If no keyword is specified in which we have to sort the records in the column, it will take its default value. `It sorts the records in ascending order by default.

Syntax

• Sort according to one column

We use ASC and DESC keywords to sort a column in ascending and descending order, respectively.

SYNTAX:

• Sort according to multiple columns

Multiple columns are sorted simultaneously by separating their names by the comma(,) operator. The names of the two columns must be unique. The records are first sorted by the first column and then the sorted list by the second column if two rows had some value earlier.

SYNTAX:

• Sort the records in ascending order

ASC keyword is used to sort the records in ascending order. SYNTAX:

• Sort the records in descending order

DESC keyword is used to sort the records in descending order. SYNTAX:

• Sort the records in ascending order without using the ASC keyword

When ASC or DESC keyword is not specified, the ORDER BY clause sorts the records in ascending order.

Examples

Consider the table Employees having the following records:

• Sort according to a single column

SELECT * FROM Employees ORDER BY NAME ASC;

This SQL query will sort all the records by their names in ascending order.

• Sort according to multiple columns

SELECT * FROM Employees ORDER BY NAME ASC, ID DESC;

This SQL Query collects all the Employees data and sorts it first according to NAME in ascending order and then according to their ID in descending order.

Note: You can get the same output by skipping the ASC keyword in the SQL Query as it is the default value of the ORDER By clause.

SELECT * FROM Employees ORDER BY NAME, ID DESC;

• Sorting without using ASC/DESC attribute

If the keywords are not specified, the records sort in ascending order by taking the default value of the ORDER BY clause. This method is not for sorting in descending order.

SELECT * FROM Employees ORDER BY NAME;

• Using both ASC and DESC attributes

When sorting your records in SQL using the ORDER BY clause, you can use both ASC and DESC keywords to perform sorting in a single SELECT statement.

SELECT * FROM Employees ORDER BY NAME ASC, ID DESC;

This example returns all the records sorted by NAME in ascending order and then a secondary sorting by ID in descending order. It happens only when there are records with the same names. It gets sorted according to the ID in descending order.

• Sorting in descending order

SELECT * FROM Employees ORDER BY NAME DESC;

This SQL Query sorts the records in the table in the descending order of their names.

• Sorting by relative position or column number

The column number is an integer that determines the relative position of the column in a table. It must be greater than 0 but not the number of columns so that the column is present in the table.

It disturbs the readability of the code and makes it difficult to predict. The sequence in which columns occur depends on the physical order of the table. The physical order often gets changed by adding, deleting, or updating the table. Thus, the query using relative position gives a different result on changing the order of columns in the table. Referring to the ORDER BY clause by column names has no impact on the results making it more suitable.

SELECT * FROM Employees ORDER BY 3 ASC;

In this example, we have sorted the table using column number 3, i.e., AGE ascending order.