Cloud

ORDER BY

The ORDER BY clause sorts the rows returned by a SELECT statement.

Syntax

SELECT columns
FROM table_name
ORDER BY sort_expression1 [ASC | DESC] [NULLS FIRST | NULLS LAST];

Parameters

  • columns: The columns to retrieve.

  • table_name: The table to retrieve records from.

  • ORDER BY: The expression used to order the results.

  • ASC or DESC: Optional. Specifies whether results are returned in ascending or descending order. Default is ASC.

  • NULLS FIRST or NULLS LAST: Optional. Specifies where NULL values appear in the sort order. NULLS FIRST places NULL values before non-null values. NULLS LAST places NULL values after non-null values. The default is NULLS LAST for ASC order and NULLS FIRST for DESC order.

Examples

The following examples use a table called salaryemp. To create the table, run the query:

CREATE TABLE salaryemp
(
    emp_id int,
    emp_name text,
    emp_div text,
    emp_sal int
);

INSERT INTO salaryemp
VALUES
(1002, 'Mike', 'Marketing', 6000),
(1003, 'Sean', 'Marketing', 6500),
(1004, 'Victor', 'Finance', 7000),
(1005, 'Lewis', 'Sales', 5500),
(1006, 'David', 'Marketing', 8000),
(1007, 'Meghan', 'Finance', 7500),
(1008, 'Harry', 'Operations', 4500),
(1009, 'Steve', 'Marketing', 6800),
(1010, 'Omar', 'Finance', 8000),
(1011, 'David', 'Sales', 8200);

To verify the inserted values, run:

SELECT * FROM salaryemp;

The query returns:

+-----------+------------+----------------+-------------+
| emp_id    | emp_name   | emp_div        | emp_sal     |
+-----------+------------+----------------+-------------+
| 1002      | Mike       | Marketing      | 6000        |
| 1003      | Sean       | Marketing      | 6500        |
| 1004      | Victor     | Finance        | 7000        |
| 1005      | Lewis      | Sales          | 5500        |
| 1006      | David      | Marketing      | 8000        |
| 1007      | Meghan     | Finance        | 7500        |
| 1008      | Harry      | Operations     | 4500        |
| 1009      | Steve      | Marketing      | 6800        |
| 1010      | Omar       | Finance        | 8000        |
| 1011      | David      | Sales          | 8200        |
+-----------+------------+----------------+-------------+

ORDER BY in ascending order

This example uses the ORDER BY clause to sort employees by their division:

SELECT emp_name, emp_div
FROM salaryemp
ORDER BY emp_div;

The query returns:

+------------+----------------+
| emp_name   | emp_div        |
+------------+----------------+
| Victor     | Finance        |
| Omar       | Finance        |
| Meghan     | Finance        |
| Mike       | Marketing      |
| Sean       | Marketing      |
| David      | Marketing      |
| Steve      | Marketing      |
| Harry      | Operations     |
| Lewis      | Sales          |
| David      | Sales          |
+------------+----------------+

ORDER BY in descending order

The following statement selects all records from the salaryemp table and sorts them by emp_sal in descending order:

SELECT * FROM salaryemp
ORDER BY emp_sal DESC;

The query returns:

+-----------+------------+----------------+-------------+
| emp_id    | emp_name   | emp_div        | emp_sal     |
+-----------+------------+----------------+-------------+
| 1011      | David      | Sales          | 8200        |
| 1006      | David      | Marketing      | 8000        |
| 1010      | Omar       | Finance        | 8000        |
| 1007      | Meghan     | Finance        | 7500        |
| 1004      | Victor     | Finance        | 7000        |
| 1009      | Steve      | Marketing      | 6800        |
| 1003      | Sean       | Marketing      | 6500        |
| 1002      | Mike       | Marketing      | 6000        |
| 1005      | Lewis      | Sales          | 5500        |
| 1008      | Harry      | Operations     | 4500        |
+-----------+------------+----------------+-------------+

ORDER BY with both ASC and DESC parameters

The following statement selects all records from the salaryemp table and sorts the rows by emp_sal in ascending order and emp_div in descending order:

SELECT * FROM salaryemp
ORDER BY emp_sal ASC, emp_div DESC;

The query returns:

+-----------+------------+----------------+-------------+
| emp_id    | emp_name   | emp_div        | emp_sal     |
+-----------+------------+----------------+-------------+
| 1008      | Harry      | Operations     | 4500        |
| 1005      | Lewis      | Sales          | 5500        |
| 1002      | Mike       | Marketing      | 6000        |
| 1003      | Sean       | Marketing      | 6500        |
| 1009      | Steve      | Marketing      | 6800        |
| 1004      | Victor     | Finance        | 7000        |
| 1007      | Meghan     | Finance        | 7500        |
| 1006      | David      | Marketing      | 8000        |
| 1010      | Omar       | Finance        | 8000        |
| 1011      | David      | Sales          | 8200        |
+-----------+------------+----------------+-------------+

ORDER BY with TEXT data types

In this example, two small tables are created with text data:

CREATE TABLE strings
(
    column1 text
);

INSERT INTO strings
VALUES ('A'), ('B'), ('a'), ('b');

CREATE TABLE texts
(
    column1 TEXT
);

INSERT INTO texts
VALUES ('A'), ('B'), ('a'), ('b');

When using the ORDER BY clause with these data types, records with uppercase letters are sorted lexicographically first, followed by records with lowercase letters.

SELECT * FROM strings ORDER BY column1;
SELECT * FROM texts ORDER BY column1;

The query returns:

 column1
---------
 A
 B
 a
 b

ORDER BY with INTERVAL data type

For this example, create a new table called interval_data:

CREATE TABLE interval_data (
    duration INTERVAL
);

INSERT INTO interval_data (duration)
VALUES
    (INTERVAL '1 month 30 days 20 hours'),
    (INTERVAL '2 months 20 hours'),
    (INTERVAL '1 month 30 days 19 hours'),
    (INTERVAL '2 months 1 hours');

ORDER BY on an interval column sorts values by their total duration. For example, 1 month 30 days 20 hours is greater than 2 months 1 hour because 1 month equals 30 days, making the first interval equivalent to 60 days 20 hours versus 60 days 1 hour.

SELECT * FROM interval_data ORDER BY duration;

The query returns:

        duration
------------------------
 2 mons 01:00:00
 1 mon 30 days 19:00:00
 1 mon 30 days 20:00:00
 2 mons 20:00:00

This total-duration sorting also applies when mixing hours and days. For example, 24 hours 5 minutes and 1 day 5 minutes represent the same total duration:

INSERT INTO interval_data (duration)
VALUES
    (INTERVAL '24 hours 5 minutes'),
    (INTERVAL '1 day 5 minutes'),
    (INTERVAL '1 day 2 minutes');
SELECT * FROM interval_data ORDER BY duration;

The query returns:

        duration
------------------------
 1 day 00:02:00
 24:05:00
 1 day 00:05:00
 2 mons 01:00:00
 1 mon 30 days 19:00:00
 1 mon 30 days 20:00:00
 2 mons 20:00:00