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 and OFFSET clauses to limit the number of rows returned by the SELECT 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 or NULLS LAST keywords to specify how NULL values should be sorted. For example:

SELECT first_name, last_name, salary FROM employees ORDER BY salary DESC NULLS LAST;

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 the ORDER 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 and FETCH 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.