SQL ORDER BY

In this SQL tutorial, we will learn how to use the ORDER BY clause in SQL. Order By in SQL is used to sort the data in ascending or descending order.

Table of Contents

• • SQL ORDER BY

• • ORDER BY in SQL

• • SQL ORDER BY Clause Examples

• • ORDER BY with LIMIT Clause

• • ORDER BY with WHERE Clause

• • Sorting Results in a Preferred Order

• • Important Points About ORDER BY Clause in SQL

ORDER BY in SQL

SQL Order By is used to sort the data in ascending or descending order. It sorts the data in ascending order by default. To sort the data in descending order we use the DESC keyword.

Syntax of ORDER By in SQL

SELECT column FROM table_name
ORDER BY column1 ASC/DESC;

Explanation:

• • table_name: it is the name of the table

• • ASC: it is used to sort the data in ascending order.

• • DESC: it is used to sort the data in descending order.

• • Column: it is the name of the column on the basis of which we sort the data.

SQL ORDER BY Clause Examples

Let’s understand the ORDER BY clause with an example:

Suppose we have a table

Table: Employees

Example 1: Sort According To a Single Column using ORDER BY Clause

Query:

SELECT Name, Department, Salary
FROM Employees
ORDER BY Salary ASC;

Output:

Name        Department      Salary

Eva           HR                      45000

Alice          HR                     50000

Charlie       Finance             55000

Bob            IT                       60000

David         IT                       70000

Explanation: here we sorted the table on the basis of the salary in ascending order.

Example 2: Sort According To Multiple Columns using ORDER BY Clause

Query:

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

Output:

Explanation: here we sorted the table on the basis of the department in ascending order and salary in descending order.

Master SQL for Data Mastery!

Learn Industry-Level SQL Skills to Analyze, Manage, and Unlock the Power of Data

Explore Program

Sorting By Column Number (instead of name)

We can also sort the data by column number instead of using column name.

Syntax:

ORDER BY Column_Number ASC/DESC

Example of Sorting By Column Number

Let’s understand this concept with an example. We can use the above table to understand the example:

Query:

SELECT Name, Department, Salary
FROM Employee
ORDER BY 3;

Output:

Name     Department    Salary

Eva         HR                  45000

Alice        HR                 50000

Charlie    Finance          55000

Bob         IT                    60000

David      IT                    70000

Explanation: we have sorted the table on the basis of the 3rd column that is (salary).

ORDER BY with LIMIT Clause

The ORDER BY command is used with the LIMIT clause in SQL to sort the query results and then pick only a specified number of rows from the sorted lists. It basically restricts the number of rows that are returned.

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT number_of_rows;

Example 1: Retrieve Top 3 Salaries

Query:

SELECT Name, Salary
FROM Employee
ORDER BY Salary DESC;
LIMIT 3;

Output:

Name     Department   Salary

David     IT                     70000

Bob        IT                     60000

Charlie   Finance           55000

Explanation:

This query returns the top 3 records of the table arranged in descending order.

Free SQL Course – Master Data Management for Industry Success!

Gain in-demand skills to manage and analyze data effectively

Explore Program

ORDER BY with WHERE Clause

The WHERE clause filters the rows based on some condition. Then, sort the data in ascending or descending order.

Syntax:

SELECT column1, column2, …
FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC];

Example: Find the Employee whose Salary > 50000 in ascending order.

Query:

SELECT Name, Department, Salary
FROM Employee
WHERE Salary > 50000
ORDER BY Salary ASC;

Output:

Name     Department   Salary

Charlie    Finance         55000

Bob         IT                   60000

David      IT                   70000

Sorting Results in a Preferred Order

We can also sort the results according to our preferences. We use CASE statements to sort the table on our preference.

Syntax:

SELECT column1, column2
FROM table_name
ORDER BY
CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE valueN
END;

Example: Suppose we want to sort the table based on the preference of the department (Finance, IT, HR) respectively.

Query:

SELECT Name, Department, Salary
FROM Employee
ORDER BY
CASE
WHEN Department = 'Finance' THEN 1
WHEN Department = 'IT' THEN 2
WHEN Department = 'HR' THEN 3
ELSE 4
END;

Output:

Name     Department     Salary

Charlie    Finance            55000

Bob         IT                      60000

David      IT                      70000

Alice       HR                    50000

Eva        HR                     45000

Important Points About ORDER BY Clause in SQL

Here are the following important points about ORDER BY clause in SQL:

• • ORDER BY clause is used to sort the data in ascending or descending order.

• • ORDER BY clause is used with WHERE clause, HAVING clause, and GROUP BY clause

• • We can also sort the data with one column, multi-column, and also with the column number.

Conclusion

In conclusion, SQL Order By is used to sort the data in ascending or descending order. It is a fundamental command of SQL that is used most of the time. ORDER BY command is used with the WHERE clause and HAVING clause, also with LIMIT offset to restrict the number of rows returned.  So far in this article, we have learned how we can use the ORDER BY clause and its example in more detail. If you’re looking to dive deeper into SQL and advance your career, consider enrolling in Intellipaat’s comprehensive SQL course.