Menu
MySQL

Sorting and Limiting results

In SQL, the ORDER BY, LIMIT, and OFFSET clauses are used to sort, limit, and paginate the result set, respectively. They provide powerful ways to manage the presentation of data in your queries.

 

 

1. ORDER BY Clause

The ORDER BY clause is used to sort the result set by one or more columns, either in ascending (ASC) or descending (DESC) order. By default, it sorts in ascending order if no direction is specified.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

 

Example 1: Ordering by a Single Column (Ascending)

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary ASC;

 

This query retrieves employee names and their salaries and orders the result by salary in ascending order.

 

Example 2: Ordering by Multiple Columns

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY DepartmentID ASC, Salary DESC;
 

In this query, the result is first sorted by DepartmentID in ascending order, and then within each department, employees are sorted by salary in descending order.

 

 

2. LIMIT Clause

The LIMIT clause is used to limit the number of rows returned by the query. This is especially useful when you are only interested in retrieving a subset of the total result.

Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;
 

Example 1: Limiting the Result Set to a Fixed Number of Rows

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;
 

This query retrieves the top 5 employees with the highest salaries.

 

 

3. OFFSET Clause

The OFFSET clause allows you to skip a certain number of rows before returning the result. It is commonly used in conjunction with LIMIT to paginate results.

Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows OFFSET number_of_rows_to_skip;

 

Example 1: Paginating Results (Using LIMIT with OFFSET)

SELECT FirstName, LastName, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5 OFFSET 10;
 

This query retrieves 5 employees, starting from the 11th row (skipping the first 10 rows). This is useful for pagination where the user might want to see page 2 or beyond.

 

 

MySQL