Order By in SQL
The ORDER BY
clause is used to sort the results of a SELECT
statement in SQL.
Here is the basic syntax of the ORDER BY
clause:
SELECT column1, column2, ... FROM table ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
The ORDER BY
clause specifies the columns that the results should be sorted by. The ASC
keyword indicates that the results should be sorted in ascending order (from low to high), while the DESC
keyword indicates that the results should be sorted in descending order (from high to low).
Here is an example of a SELECT
statement that uses the ORDER BY
clause to sort the results by the last_name
column in ascending order:
SELECT first_name, last_name, salary FROM employees ORDER BY last_name ASC;
This SELECT
statement will retrieve the first_name
, last_name
, and salary
columns from the employees
table, and sort the results by the last_name
column in alphabetical order.
Points to consider when working with the Order By in SQL
- You can specify multiple columns in the
ORDER BY
clause to sort the results by multiple columns. For example:
SELECT first_name, last_name, salary FROM employees ORDER BY last_name ASC, first_name ASC;
This SELECT
statement will retrieve the first_name
, last_name
, and salary
columns from the employees
table, and sort the results first by the last_name
column in alphabetical order, and then by the first_name
column in alphabetical order.
- You can use the
LIMIT
andOFFSET
clauses to limit the number of rows returned by theSELECT
statement. For example:
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC LIMIT 10 OFFSET 20;
This SELECT
statement will retrieve the first_name
, last_name
, and salary
columns from the employees
table, sort the results by the salary
column in descending order, and return the top 10 rows starting from the 21st row.
- You can use the
NULLS FIRST
orNULLS LAST
keywords to specify howNULL
values should be sorted. For example:
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC NULLS LAST;
This SELECT
statement will retrieve the first_name
, last_name
, and salary
columns from the employees
table, sort the results by the salary
column in descending order, and place the rows with NULL
values in the salary
column at the end of the result set.
- You can use the
CASE
expression to specify custom sorting rules. For example:
SELECT first_name, last_name, salary, department FROM employees ORDER BY (CASE WHEN department = 'Sales' THEN 1 WHEN department = 'Marketing' THEN 2 ELSE 3 END), salary DESC;
This SELECT
statement will retrieve the first_name
, last_name
, salary
, and department
columns from the employees
table, and sort the results first by the department
column in the following order: "Sales", "Marketing", and all other values. Within each department
value, the results will be sorted by the salary
column in descending order.
- You can use the
COLLATE
keyword to specify a collation (i.e. a set of rules for comparing characters in a string) for theORDER BY
clause. For example:
SELECT first_name, last_name FROM employees ORDER BY last_name COLLATE "fr_FR" ASC;
This SELECT
statement will retrieve the first_name
and last_name
columns from the employees
table, and sort the results by the last_name
column in alphabetical order using the French collation "fr_FR".
- You can use the
OFFSET
andFETCH
clauses to specify the number of rows to skip and the number of rows to return, respectively. For example:
SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
This SELECT
statement is equivalent to the one in the previous example, which used the LIMIT
and OFFSET
clauses to retrieve the top 10 rows starting from the 21st row.