ORDER BY in SQL
NOTE: This tutorial uses ANSI SQL or Standard SQL. However, the ORDER BY clause is imported by many database engines. Therefore, you can use this tutorial to implement ORDER BY in MySQL, SQL Server, PostgreSQL, etc.”
SQL Server ORDER BY
The ORDER BY clause in SQL allows you to sort the result of a query based on a given condition in ascending or descending order.
In most cases, you will find the ORDER BY clause used in SELECT statements, but it can be included in other queries.
The following shows the syntax of the ORDER BY clause in SQL.
[{ ASC | DESC }]
[{ NULLS FIRST | NULLS LAST }]
[, ...]
The ORDER BY clause allows you to specify a column or expression used as the sorting criteria.
The ASC | DESC parameters define the sorting order, with ASC representing ascending order and DESC denoting descending order. The ORDER BY clause will default sort the values in ascending order.
The NULLS FIRST | NULLS LAST clauses tell the ORDER BY clause whether to include NULL values before non-null or after non-null values.
Example Usage
Let us explore some examples demonstrating the usage of the ORDER BY clause.
Example 1 – Basic Usage
The following example shows how to use the ORDER BY clause with a SELECT statement.
col1,
col2
FROM (
SELECT
1 AS col1,
TRUE AS col2
UNION ALL
SELECT
10,
TRUE
UNION ALL
SELECT
NULL,
FALSE)
ORDER BY
col1;
In this case, the query contains two columns, each holding a numerical, Boolean, or NULL value. We then use the ORDER BY clause to sort the values in ascending order.
The resulting output is as shown:
NULL FALSE
1 TRUE
10 TRUE
To sort the values in descending order, we can add the DESC keyword:
col1,
col2
FROM (
SELECT
1 AS col1,
TRUE AS col2
UNION ALL
SELECT
10,
TRUE
UNION ALL
SELECT
NULL,
FALSE)
ORDER BY
col1 DESC;
In this case, the query should return the following:
10 TRUE
1 TRUE
NULL FALSE
Using the NULLS FIRST clause, you can also sort by descending order and allow NULL values to be included first.
Example:
col1,
col2
FROM (
SELECT
1 AS col1,
TRUE AS col2
UNION ALL
SELECT
10,
TRUE
UNION ALL
SELECT
NULL,
FALSE)
ORDER BY
col1 DESC NULLS FIRST;
Resulting output:
NULL FALSE
10 TRUE
1 TRUE
In this case, the NULL value is considered first, then other values are ordered in descending order.
Example 2 – Using ORDER BY Clause With Database Table
The following example shows rows in the table below using the ORDER BY clause.
To order the rows based on the manufacturer, we can run the query as follows:
By default, the query will order the values in ascending order as shown:
To order the values in descending order, run the following:
Output:
Conclusion
In this post, we discussed how to use the ORDER BY clause in Standard SQL, allowing you to sort the rows in a result in ascending or descending order.
Source: linuxhint.com