ORDER BY Clause with LIMIT and OFFSET

The ORDER BY clause is used in SQL to sort the result set in ascending or descending order based on one or more columns. However, in some cases, we may not need to display the entire sorted list of records. In such cases, we can use the LIMIT and OFFSET keywords along with the ORDER BY clause to restrict the number of records in the result set.

Syntax

The syntax of the ORDER BY clause with LIMIT and OFFSET is as follows:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... [ASC | DESC]
LIMIT num_of_records OFFSET offset_value;

• column1, column2, ... - columns based on which we want to sort the result set.
• table_name - name of the table from which we want to retrieve the records.
• ASC or DESC - specifies whether we want to sort the result set in ascending or descending order. (Optional)
• num_of_records - the maximum number of records to be displayed in the result set.
• offset_value - the starting point (offset) from where we want to retrieve the records. (Optional)

Example

Consider the following table employees:

id	name	age	salary
1	Alice	25	5000
2	Bob	30	6000
3	Charlie	27	5500
4	David	28	5700
5	Eve	23	4800
6	Frank	32	6200

To display the top three earning employees, we can use the following SQL query:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

The above query will sort the employees in descending order of salary and display the top three earning employees based on the LIMIT keyword.

Output

The output of the above SQL query will be:

name	salary
Frank	6200
Bob	6000
David	5700

Explanation

In the above example, we have used the ORDER BY clause to sort the employees table in descending order of salary. Then we have used the LIMIT keyword to restrict the number of records in the result set to three.

This query will only display the top three earning employees, starting from the first record. However, in some cases, we may want to skip a few records and start displaying from a specific record. That's where the OFFSET keyword comes into play.

Use

The ORDER BY clause with LIMIT and OFFSET is immensely useful when we need to retrieve a small subset of records from a larger result set, based on some specific criteria. For example, if we want to display the top ten students from a class, we can sort the result set based on their grades and use the LIMIT keyword to restrict the result set to ten records.

Important Points

• The ORDER BY clause is always used after the FROM clause.
• The LIMIT and OFFSET keywords are optional.
• The LIMIT keyword always comes after the ORDER BY clause.
• The OFFSET keyword is used to skip a specified number of records in the result set before displaying the desired records.
• If we don't specify the ASC or DESC keyword after the ORDER BY clause, the default sorting order is ASC (ascending).

Summary

The ORDER BY clause with LIMIT and OFFSET is used to sort the result set based on one or more columns and display a subset of records from the sorted list. This is especially useful when we have a large result set and need to display only a small number of records. The LIMIT keyword is used to restrict the number of records in the result set, while the OFFSET keyword is used to skip a specified number of records before displaying the desired records.