SQL - Select Order By Limit

The ORDER BY clause with the LIMIT clause in SQL is used to retrieve a specified number of rows from a result set based on a defined sorting order. It allows you to sort the result set using the ORDER BY clause and limit the number of rows returned using the LIMIT clause. This combination is commonly used to retrieve the top or bottom records based on a specific sorting criterion.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
LIMIT n;
--or
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
LIMIT offset, n;

Example:

Let's assume we have a "Products" table with the following columns:

ProductID (unique identifier for each product)

ProductName (name of the product)

Price (price of the product)

To retrieve the top 5 most expensive products from the "Products" table, we can use the ORDER BY clause with the LIMIT clause:

SELECT ProductID, ProductName, Price
FROM Products
ORDER BY Price DESC
LIMIT 5;

Explanation:

The SELECT statement specifies the columns we want to retrieve from the table. In this case, we select ProductID, ProductName, and Price columns.

The FROM clause indicates the table from which we want to retrieve data. In this example, it's the "Products" table.

The ORDER BY clause is used to specify the sorting order for the result set. In this case, we sort the rows based on the Price column in descending order (DESC).

The LIMIT clause is used to restrict the number of rows returned. In this case, we limit the result set to 5 rows.

By executing this query, you will retrieve the top 5 most expensive products from the "Products" table based on their price.

LIMIT with Offset:

You can also use the LIMIT clause with an offset value to skip a certain number of rows before retrieving the specified number of rows. The syntax for using the LIMIT clause with an offset is as follows:

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

For example, to retrieve the 6th to 10th most expensive products, you can use the following query:

SELECT ProductID, ProductName, Price
FROM Products
ORDER BY Price DESC
LIMIT 5, 5;

This query skips the first 5 rows (OFFSET 5) and retrieves the next 5 rows (LIMIT 5).