SQL ORDER BY

SQL ORDER BY is used to sort the result set of a query in either ascending (ASC) or descending (DESC) order. By default, ORDER BY sorts in ascending order. Sorting can be applied to one or more columns, which helps organize and analyze data effectively.

Example: First, we will create a demo SQL database and table, on which we will use the ORDER BY command.

Query:

SELECT EmpID, Name, Department, Salary
FROM Employees
ORDER BY
 Salary DESC; 

Output:

Syntax:

SELECT * FROM table_name ORDER BY column_name ASC | DESC; 

• table_name: name of the table.
• column_name: name of the column according to which the data is needed to be arranged.
• ASC: to sort the data in ascending order.
• DESC: to sort the data in descending order.

Examples

We will use the following Student table to demonstrate the SQL ORDER BY clause.

Now consider the above database table and find the results of different queries.

Example 1 : Sort by a Single Column

In this example, we will fetch all data from the table Student and sort the result in descending order according to the column ROLL_NO. 

Query:

SELECT * FROM students ORDER BY ROLL_NO DESC;

Output:

In the above example, if we want to sort in ascending order we have to use ASC in place of DESC.

Example 2 : Sort by Multiple Columns

In this example, we sort the records by age in descending order and then by name in ascending order. To sort by multiple columns, separate the column names with commas.

Query:

SELECT * FROM students ORDER BY age DESC , name ASC; 

Output:

The result is first sorted by Age in descending order. For rows with the same Age, it’s further sorted by Name in ascending order.

Sorting By Column Number

Instead of using column names, you can sort results using the position of a column in the SELECT list. The number must be greater than 0 and not exceed the number of selected columns.

Syntax:

ORDER BY Column_Number asc/desc;

Example of Sorting By Column Number

First, create a table and insert the required records into it. The Student_info table is shown below.

Query:

Next, sort the records based on the Roll_no column using the ORDER BY clause. The query is as follows:

SELECT Roll_no, Name, Address
FROM studentinfo
ORDER BY 1

Output: