Appearance

Reference>Meta Database>SQL Command Reference>Query Syntax>ORDER BY

ORDER BY ​

ORDER BY returns the results of a SELECT list in a sorted order.

Syntax ​

sql
SELECT ...
FROM ...
ORDER BY orderItem [, orderItem , ...]
[ ... ]

where

sql
orderItem ::= 
    {col_alias | position | expr} 
    [ASC | DESC] [NULLS {FIRST | LAST}]

Parameters ​

col_alias: Alias of a column as it appears in the SELECT statement list.

position: Position of the alias, column name, or expression as it appears in the SELECT list.

expr: SQL expression.

ASC | DESC (optional): Defines the sorting order of the sort key: ascending (ASC) or descending (DESC). The default is ASC.

NULLS FIRST | LAST (optional): Defines how NULL values are positioned in the result. NULLS FIRST means that NULL values appear before other values; NULLS LAST means they appear after other values.

Usage Notes ​

  • Leading zeros before the decimal point and trailing zeros after the decimal point have no effect on the sort order of numeric sort keys.

  • By default, NULL values are treated as "greater than all other values". Therefore, the position of NULL values depends on the specified sort order:

    • When ASC (ascending) is used, NULL values are returned after all other values by default. Use NULLS FIRST to explicitly place them before all other values.
    • When DESC (descending) is used, NULLS LAST ensures that NULL values appear after all others.

  • Using ORDER BY within a subquery or in an OVER() clause affects only that specific subquery or clause.

  • ORDER BY can be resource-intensive. It's recommended to use it primarily at the top-level query and more sparingly in subqueries.

Example 1 | Textual Sort Key ​

sql
WITH
tab1 AS  ( SELECT * FROM (
                    SELECT 'a' AS ord, 'g1' AS grp, '10' AS col1,  NULL AS col2
                    UNION ALL 
                    SELECT 'b' AS ord, 'g2' AS grp, '20' AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'c' AS ord, 'g1' AS grp, '30' AS col1, 'b' AS col2
                    UNION ALL
                    SELECT 'd' AS ord, 'g2' AS grp, '40' AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'e' AS ord, 'g1' AS grp, '50' AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'f' AS ord, 'g2' AS grp, '40' AS col1, 'd' AS col2
                    UNION ALL
                    SELECT 'g' AS ord, 'g1' AS grp, '50' AS col1, 'd' AS col2
               ))
SELECT ord FROM tab1
ORDER BY ord DESC;
+-----+
| ORD |
+-----+
| g   |
| f   |
| e   |
| d   |
| c   |
| b   |
| a   |
+-----+

Example 2 | Numeric Sort Key ​

sql
WITH
tab1 AS (
    SELECT * FROM ( SELECT 'a' AS ord, 'g1' AS grp, 10 AS col1, NULL AS col2
                    UNION ALL 
                    SELECT 'b' AS ord, 'g2' AS grp, 20 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'c' AS ord, 'g1' AS grp, 30 AS col1, 'b' AS col2
                    UNION ALL
                    SELECT 'd' AS ord, 'g2' AS grp, 40 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'e' AS ord, 'g1' AS grp, 50 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'f' AS ord, 'g2' AS grp, 40 AS col1, 'd' AS col2
                    UNION ALL
                    SELECT 'g' AS ord, 'g1' AS grp, 50 AS col1, 'd' AS col2
               ))
SELECT col1, ord FROM tab1
ORDER BY col1;
+------+-----+
| COL1 | ORD |
+------+-----+
| 10   | a   |
| 20   | b   |
| 30   | c   |
| 40   | d   |
| 40   | f   |
| 50   | e   |
| 50   | g   |
+------+-----+

Example 3 | NULL Values ​

Compare queries and results 1 and 2, 3 and 4.

Query 1 ​

sql
WITH
tab1 AS (
    SELECT * FROM ( SELECT 'a' AS ord, 'g1' AS grp, 10 AS col1, NULL AS col2
                    UNION ALL 
                    SELECT 'b' AS ord, 'g2' AS grp, 20 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'c' AS ord, 'g1' AS grp, 30 AS col1, 'b' AS col2
                    UNION ALL
                    SELECT 'd' AS ord, 'g2' AS grp, 40 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'e' AS ord, 'g1' AS grp, 50 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'f' AS ord, 'g2' AS grp, 40 AS col1, 'd' AS col2
                    UNION ALL
                    SELECT 'g' AS ord, 'g1' AS grp, 50 AS col1, 'd' AS col2
               ))
SELECT col1, col2 FROM tab1
ORDER BY col2 DESC;
+------+------+
| COL1 | COL2 |
+------+------+
| 10   | NULL |
| 40   | d    |
| 50   | d    |
| 30   | b    |
| 50   | a    |
| 20   | a    |
| 40   | a    |
+------+------+

Query 2 ​

sql
WITH
tab1 AS (
    SELECT * FROM ( SELECT 'a' AS ord, 'g1' AS grp, 10 AS col1, NULL AS col2
                    UNION ALL 
                    SELECT 'b' AS ord, 'g2' AS grp, 20 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'c' AS ord, 'g1' AS grp, 30 AS col1, 'b' AS col2
                    UNION ALL
                    SELECT 'd' AS ord, 'g2' AS grp, 40 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'e' AS ord, 'g1' AS grp, 50 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'f' AS ord, 'g2' AS grp, 40 AS col1, 'd' AS col2
                    UNION ALL
                    SELECT 'g' AS ord, 'g1' AS grp, 50 AS col1, 'd' AS col2
               ))
SELECT col1, col2 FROM tab1
ORDER BY col2 DESC NULLS LAST;
+------+------+
| COL1 | COL2 |
+------+------+
| 40   | d    |
| 50   | d    |
| 30   | b    |
| 50   | a    |
| 20   | a    |
| 40   | a    |
| 10   | NULL |
+------+------+

Query 3 ​

sql
WITH
tab1 AS (
    SELECT * FROM ( SELECT 'a' AS ord, 'g1' AS grp, 10 AS col1, NULL AS col2
                    UNION ALL 
                    SELECT 'b' AS ord, 'g2' AS grp, 20 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'c' AS ord, 'g1' AS grp, 30 AS col1, 'b' AS col2
                    UNION ALL
                    SELECT 'd' AS ord, 'g2' AS grp, 40 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'e' AS ord, 'g1' AS grp, 50 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'f' AS ord, 'g2' AS grp, 40 AS col1, 'd' AS col2
                    UNION ALL
                    SELECT 'g' AS ord, 'g1' AS grp, 50 AS col1, 'd' AS col2
               ))
SELECT col1, col2 FROM tab1
ORDER BY col2 ASC;
+------+------+
| COL1 | COL2 |
+------+------+
| 40   | a    |
| 50   | a    |
| 20   | a    |
| 30   | b    |
| 50   | d    |
| 40   | d    |
| 10   | NULL |
+------+------+
sql
WITH
tab1 AS (
    SELECT * FROM ( SELECT 'a' AS ord, 'g1' AS grp, 10 AS col1, NULL AS col2
                    UNION ALL 
                    SELECT 'b' AS ord, 'g2' AS grp, 20 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'c' AS ord, 'g1' AS grp, 30 AS col1, 'b' AS col2
                    UNION ALL
                    SELECT 'd' AS ord, 'g2' AS grp, 40 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'e' AS ord, 'g1' AS grp, 50 AS col1, 'a' AS col2
                    UNION ALL
                    SELECT 'f' AS ord, 'g2' AS grp, 40 AS col1, 'd' AS col2
                    UNION ALL
                    SELECT 'g' AS ord, 'g1' AS grp, 50 AS col1, 'd' AS col2
               ))
SELECT col1, col2 FROM tab1
ORDER BY col2 ASC NULLS FIRST;
+------+------+
| COL1 | COL2 |
+------+------+
| 10   | NULL |
| 20   | a    |
| 40   | a    |
| 50   | a    |
| 30   | b    |
| 40   | d    |
| 50   | d    |
+------+------+
© 2025 MetaKraftwerk GmbH. All Rights Reserved.