ORDER BY LIMIT - SQL

In SQL, the ORDER BY clause combined with the LIMIT clause is commonly used to retrieve a subset of rows from a table while controlling the order in which the rows are returned. The ORDER BY clause specifies the sorting criteria, and the LIMIT clause limits the number of rows returned in the result set.

The syntax for using the ORDER BY clause with the LIMIT clause in SQL is as follows:

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

Where

• column1, column2, ...: Represents the columns used for sorting the result set.
• table_name: Specifies the name of the table from which data is retrieved.
• ASC or DESC: Specifies the sort order as ascending (ASC) or descending (DESC), respectively, for each column listed in the ORDER BY clause.
• row_count: Specifies the maximum number of rows to be returned in the result set.

Example

Consider a scenario where you have a table named "employees" containing employee information, and you want to retrieve the top 10 highest-paid employees. You can use the ORDER BY clause with the LIMIT clause as follows:

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 10;

In this example, the query retrieves all columns from the "employees" table, orders the result set by the "salary" column in descending order (highest to lowest salary), and limits the result set to the first 10 rows.

Conclusion

The combination of the ORDER BY clause with the LIMIT clause in SQL provides a powerful mechanism for retrieving a sorted subset of rows from a table. By understanding its syntax, usage, and considerations, SQL developers can effectively control the ordering and limit the number of rows returned in query results, catering to various reporting and analysis requirements.