Controlling the Order of Rows Returned in SQL
Using the ORDER BY Clause
The ORDER BY clause is the primary tool used to sort the results of a query. You can specify one or more columns to sort by and indicate whether the sort should be in ascending or descending order.
Syntax
SELECT column1, column2, ... FROM table ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
Key Points
- Ascending (ASC) Order: By default, ORDER BY sorts in ascending order. If you omit the ASC keyword, it will sort in ascending order.
- Descending (DESC) Order: Use DESC to sort in descending order.
- Multiple Columns: You can sort by multiple columns. The sorting is done first by the first column, then by the second if there are ties, and so on.
Examples
Sorting by One Column:
SELECT Nom, Salaire FROM Employes ORDER BY Salaire DESC;
This query retrieves employee names and salaries, sorting them by salary in descending order.
Sorting by Multiple Columns:
SELECT Nom, Département, Salaire FROM Employes ORDER BY Département ASC, Salaire DESC;
This query sorts employees first by department in ascending order and then by salary in descending order within each department.
Controlling Row Order with ROW_NUMBER()
When you need to assign a unique sequential number to rows in the result set, you can use the ROW_NUMBER() window function. This can be particularly useful for pagination or ranking.
Syntax
SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1 [ASC|DESC]) AS rownum FROM table;
Key Points
- Partitioning: ROW_NUMBER() can be used with the PARTITION BY clause to reset the row number for each partition of data.
- Pagination: Useful for implementing pagination by returning a subset of rows based on row numbers.
Examples
Simple Row Numbering:
SELECT Nom, Salaire, ROW_NUMBER() OVER (ORDER BY Salaire DESC) AS rang FROM Employes;
This query assigns a rank to employees based on their salary, with the highest salary getting rank 1.
Row Numbering with Partitioning:
SELECT Nom, Département, Salaire, ROW_NUMBER() OVER (PARTITION BY Département ORDER BY Salaire DESC) AS rang FROM Employes;
This query assigns a rank to employees within each department based on salary, with salaries sorted in descending order within each department.
Using RANK() and DENSE_RANK()
Similar to ROW_NUMBER(), the RANK() and DENSE_RANK() window functions can be used to assign ranks to rows, but with slight differences in handling ties.
Syntax
RANK() Syntax:
SELECT column1, column2, RANK() OVER (ORDER BY column1 [ASC|DESC]) AS rank FROM table;
- DENSE_RANK() Syntax:
SELECT column1, column2, DENSE_RANK() OVER (ORDER BY column1 [ASC|DESC]) AS dense_rank FROM table;
Key Points
- RANK(): Assigns the same rank to rows with equal values but skips subsequent ranks.
- DENSE_RANK(): Assigns the same rank to rows with equal values but does not skip ranks.
Examples
Using RANK():
SELECT Nom, Salaire, RANK() OVER (ORDER BY Salaire DESC) AS rang FROM Employes;
This query ranks employees by salary, with equal salaries receiving the same rank and subsequent ranks skipped.
Using DENSE_RANK():
SELECT Nom, Salaire, DENSE_RANK() OVER (ORDER BY Salaire DESC) AS rang_dense FROM Employes;
This query ranks employees by salary, with equal salaries receiving the same rank but no ranks skipped.
Using NTILE()
The NTILE() function divides the result set into a specified number of roughly equal parts, providing a way to segment data.
Syntax
SELECT column1, column2, NTILE(number_of_buckets) OVER (ORDER BY column1 [ASC|DESC]) AS bucket FROM table;
Key Points
- Buckets: number_of_buckets specifies how many buckets (or segments) to divide the result set into.
- Usage: Useful for data distribution analysis and creating quartiles or percentiles.
Example
SELECT Nom, Salaire, NTILE(4) OVER (ORDER BY Salaire DESC) AS quartile FROM Employes;
This query divides employees into four quartiles based on their salary, with the highest salaries falling into the first quartile.
Controlling Row Order with Subqueries
You can also control row order by using subqueries to pre-sort data before applying further operations.
Syntax
SELECT * FROM ( SELECT column1, column2 FROM table ORDER BY column1 [ASC|DESC] ) AS subquery;
Key Points
- Pre-sorting: Use a subquery to pre-sort data before performing additional operations or joins.
- Nested Queries: Helpful for complex queries where initial sorting is required.
Example
SELECT * FROM ( SELECT Nom, Salaire FROM Employes ORDER BY Salaire DESC ) AS sorted_employees WHERE Salaire > 50000;
This query first sorts employees by salary in descending order and then filters those with a salary greater than 50,000.
Using FETCH FIRST and OFFSET for Pagination
Pagination is a common requirement, and SQL provides ways to handle it, particularly using the FETCH FIRST and OFFSET clauses in databases that support them.
Syntax
SELECT column1, column2 FROM table ORDER BY column1 OFFSET offset_value ROWS FETCH FIRST fetch_value ROWS ONLY;
Key Points
- OFFSET: Specifies how many rows to skip before starting to return rows.
- FETCH FIRST: Limits the number of rows returned.
Example
SELECT Nom, Salaire FROM Employes ORDER BY Salaire DESC OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY;
This query skips the first 10 rows and then returns the next 10 rows of employees, sorted by salary in descending order.