Order by, Limit

The ORDER BY clause is used to sort the results of a query, while the LIMIT clause is used to restrict the number of rows returned.

Purpose: Sorts the query results based on one or more columns.
Default Order:
- Ascending (ASC) is the default.
- Descending (DESC) can be specified for reverse order.

Syntax:

SELECT column_names
FROM table_name
ORDER BY column_name [ASC|DESC];

Examples:

Ascending Order:

SELECT employee_id, employee_name, salary
FROM Employees
ORDER BY salary ASC;

This query sorts employees by salary in ascending order.

Descending Order:

SELECT employee_id, employee_name, salary
FROM Employees
ORDER BY salary DESC;

This query sorts employees by salary in descending order.

Sorting by Multiple Columns:
```
SELECT employee_id, employee_name, salary, department
FROM Employees
ORDER BY department ASC, salary DESC;
```
This query sorts first by department (ascending) and then by salary (descending) within each department.

Purpose: Restricts the number of rows returned by a query.
Often used in combination with ORDER BY to fetch a limited number of top or bottom rows.

Syntax:

SELECT column_names
FROM table_name
ORDER BY column_name [ASC|DESC]
LIMIT row_count;

Examples:

Limit the Number of Rows:

SELECT employee_id, employee_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 5;

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

The ORDER BY clause works seamlessly with LIMIT to retrieve sorted subsets of data.

Example:

SELECT employee_id, employee_name, salary
FROM Employees
ORDER BY salary ASC
LIMIT 3;

This query retrieves the 3 employees with the lowest salaries.

ORDER BY:
- Sorts data in ascending or descending order.
- Supports sorting by multiple columns.
LIMIT:
- Restricts the number of rows in the result set.
- Often used to fetch “top-N” or “bottom-N” records.
Together:
- Use ORDER BY for sorting and LIMIT for restricting rows to create powerful queries, e.g., fetching the “top 10 highest-paid employees.”

Fetch Top Records:

SELECT * FROM Sales
ORDER BY total_revenue DESC
LIMIT 10;

Retrieve the top 10 sales records by revenue.

Fetch Bottom Records:
```
SELECT * FROM Sales
ORDER BY total_revenue ASC
LIMIT 5;
```
Retrieve the 5 sales records with the lowest revenue.
Paginate Results: Combine LIMIT with OFFSET (in some SQL dialects) for pagination:
```
SELECT * FROM Products
ORDER BY product_name ASC
LIMIT 10 OFFSET 20;
```
Fetches 10 rows starting from the 21st row.

ORDER BY: Sorts data by one or more columns (ascending or descending).
LIMIT: Limits the number of rows in the result set.
Together, they are powerful tools for filtering and sorting query results efficiently.

🍿 CornPop