Sorting and Limiting Results in SQL

When working with databases, the data retrieved using the SELECT statement is not always in the order we want. In real-world scenarios such as salary reports, leaderboards, job listings, or dashboards, data must be displayed in a meaningful order and often in limited quantities. SQL provides two very important clauses for this purpose: ORDER BY for sorting results and LIMIT for restricting the number of rows returned.

ORDER BY Clause

The ORDER BY clause is used to sort the result set of a query based on one or more columns. Sorting can be done in ascending or descending order.

Sorting in Ascending Order (Default)

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary;

Explanation

• The result is sorted by the salary column.
• By default, SQL sorts in ascending order (ASC).
• Employees with lower salaries appear first.

Ascending order means:

• Numbers → smallest to largest
• Text → A to Z
• Dates → oldest to newest

Sorting in Descending Order

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

Explanation

• DESC sorts data in reverse order.
• Highest salary appears first.
• Commonly used for rankings and top-performer reports.

Sorting by Multiple Columns

SELECT first_name, last_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Explanation

• First, rows are sorted by department in ascending order.
• If multiple employees belong to the same department, they are sorted by salary in descending order.
• This is useful when grouping related records while still ranking them internally.

Important ORDER BY Rules

• ORDER BY is always written after FROM and WHERE.
• Column names or column positions can be used.
• NULL values are usually sorted last (database-dependent).
• ORDER BY affects only the output, not the stored data.

LIMIT Clause

The LIMIT clause is used to restrict the number of rows returned by a query. This is very useful when working with large datasets or when showing data page-by-page.

Limiting the Number of Rows

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Explanation

• The query sorts employees by salary in descending order.
• LIMIT 5 ensures that only the top 5 records are returned.
• Commonly used to find top earners, recent records, or highest scores.

Using OFFSET for Pagination

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

Explanation

• LIMIT 5 → fetches 5 rows
• OFFSET 5 → skips the first 5 rows
• This returns employees ranked from 6 to 10

This technique is widely used in:

• Job portals
• E-commerce product listings
• Search result pages

LIMIT and OFFSET in Simple Terms