Understanding ORDER BY in PostgreSQL (With Examples)

Written by Team Timescale

The ORDER BY clause in PostgreSQL or TimescaleDB allows you to sort the result set of a query by one or more columns in either ascending or descending order. By default, the sorting is in ascending order. However, you can specify descending order if needed.

PostgreSQL ORDER BY: Syntax

The basic syntax of the ORDER BY clause is as follows:

SELECT column1, column2, … FROM table ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Example table

Let's consider a table weather_data with the following structure and data:

CREATE TABLE weather_data ( id SERIAL PRIMARY KEY, device_id VARCHAR(50), temperature NUMERIC, humidity NUMERIC, wind_speed NUMERIC, recorded_at TIMESTAMP );

INSERT INTO weather_data (device_id, temperature, humidity, wind_speed, recorded_at) VALUES ('device_1', 22.5, 55.0, 12.5, '2023-01-15 08:00:00'), ('device_2', 21.0, 60.0, 10.0, '2023-01-15 08:05:00'), ('device_1', 23.0, 57.0, 11.0, '2023-01-15 08:10:00'), ('device_2', 19.5, 62.0, 8.0, '2023-01-15 08:15:00'), ('device_1', 20.0, 59.0, 9.5, '2023-01-15 08:20:00'), ('device_3', 24.0, 54.0, 13.0, '2023-01-15 08:25:00'), ('device_2', 18.5, 63.0, 7.5, '2023-01-15 08:30:00'), ('device_3', 22.0, 55.5, 12.0, '2023-01-15 08:35:00'), ('device_1', 21.5, 58.0, 10.5, '2023-01-15 08:40:00'), ('device_3', 23.5, 53.0, 13.5, '2023-01-15 08:45:00');

Practical examples with the ORDER BY clause

Let's dive into some examples to see the ORDER BY clause in action.

Example 1: Sorting by a single column

Suppose we want to retrieve all weather data sorted by temperature in ascending order. We can use the ORDER BY clause to achieve this:

SELECT id, device_id, temperature, humidity, wind_speed, recorded_at FROM weather_data ORDER BY temperature ASC;

Result:

id | device_id | temperature | humidity | wind_speed | recorded_at ----+-----------+-------------+----------+------------+--------------------- 7 | device_2 | 18.5 | 63.0 | 7.5 | 2023-01-15 08:30:00 4 | device_2 | 19.5 | 62.0 | 8.0 | 2023-01-15 08:15:00 5 | device_1 | 20.0 | 59.0 | 9.5 | 2023-01-15 08:20:00 2 | device_2 | 21.0 | 60.0 | 10.0 | 2023-01-15 08:05:00 9 | device_1 | 21.5 | 58.0 | 10.5 | 2023-01-15 08:40:00 1 | device_1 | 22.5 | 55.0 | 12.5 | 2023-01-15 08:00:00 8 | device_3 | 22.0 | 55.5 | 12.0 | 2023-01-15 08:35:00 3 | device_1 | 23.0 | 57.0 | 11.0 | 2023-01-15 08:10:00 10 | device_3 | 23.5 | 53.0 | 13.5 | 2023-01-15 08:45:00 6 | device_3 | 24.0 | 54.0 | 13.0 | 2023-01-15 08:25:00

This query sorts the rows by the temperature column in ascending order.

Example 2: Sorting by multiple columns

Now, let's say we want to sort the weather data first by device_id and then by recorded_at in ascending order. We can use the ORDER BY clause with multiple columns:

SELECT id, device_id, temperature, humidity, wind_speed, recorded_at FROM weather_data ORDER BY device_id ASC, recorded_at ASC;

Result:

id | device_id | temperature | humidity | wind_speed | recorded_at ----+-----------+-------------+----------+------------+--------------------- 1 | device_1 | 22.5 | 55.0 | 12.5 | 2023-01-15 08:00:00 3 | device_1 | 23.0 | 57.0 | 11.0 | 2023-01-15 08:10:00 5 | device_1 | 20.0 | 59.0 | 9.5 | 2023-01-15 08:20:00 9 | device_1 | 21.5 | 58.0 | 10.5 | 2023-01-15 08:40:00 2 | device_2 | 21.0 | 60.0 | 10.0 | 2023-01-15 08:05:00 4 | device_2 | 19.5 | 62.0 | 8.0 | 2023-01-15 08:15:00 7 | device_2 | 18.5 | 63.0 | 7.5 | 2023-01-15 08:30:00 6 | device_3 | 24.0 | 54.0 | 13.0 | 2023-01-15 08:25:00 8 | device_3 | 22.0 | 55.5 | 12.0 | 2023-01-15 08:35:00 10 | device_3 | 23.5 | 53.0 | 13.5 | 2023-01-15 08:45:00

This query sorts the rows first by device_id and then by recorded_at in ascending order.

Example 3: Sorting by descending order

Suppose we want to retrieve the weather data sorted by humidity in descending order. We can use the ORDER BY clause with DESC:

SELECT id, device_id, temperature, humidity, wind_speed, recorded_at FROM weather_data ORDER BY humidity DESC;

Result:

id | device_id | temperature | humidity | wind_speed | recorded_at ----+-----------+-------------+----------+------------+--------------------- 7 | device_2 | 18.5 | 63.0 | 7.5 | 2023-01-15 08:30:00 4 | device_2 | 19.5 | 62.0 | 8.0 | 2023-01-15 08:15:00 2 | device_2 | 21.0 | 60.0 | 10.0 | 2023-01-15 08:05:00 5 | device_1 | 20.0 | 59.0 | 9.5 | 2023-01-15 08:20:00 9 | device_1 | 21.5 | 58.0 | 10.5 | 2023-01-15 08:40:00 3 | device_1 | 23.0 | 57.0 | 11.0 | 2023-01-15 08:10:00 8 | device_3 | 22.0 | 55.5 | 12.0 | 2023-01-15 08:35:00 1 | device_1 | 22.5 | 55.0 | 12.5 | 2023-01-15 08:00:00 6 | device_3 | 24.0 | 54.0 | 13.0 | 2023-01-15 08:25:00 10 | device_3 | 23.5 | 53.0 | 13.5 | 2023-01-15 08:45:00

This query sorts the rows by the humidity column in descending order.

Example 4: Combining ORDER BY with LIMIT

We can also combine ORDER BY with LIMIT to retrieve a specific number of sorted rows. For instance, to get the top three highest wind speeds recorded:

SELECT id, device_id, temperature, humidity, wind_speed, recorded_at FROM weather_data ORDER BY wind_speed DESC LIMIT 3;

Result:

id | device_id | temperature | humidity | wind_speed | recorded_at ----+-----------+-------------+----------+------------+--------------------- 10 | device_3 | 23.5 | 53.0 | 13.5 | 2023-01-15 08:45:00 6 | device_3 | 24.0 | 54.0 | 13.0 | 2023-01-15 08:25:00 1 | device_1 | 22.5 | 55.0 | 12.5 | 2023-01-15 08:00:00 This query sorts the rows by wind_speed in descending order and returns the top three rows.

Conclusion

The ORDER BY clause is a fundamental part of SQL and PostgreSQL that allows you to sort query results based on specified columns. To learn more about ORDER BY, check out the PostgreSQL documentation. If you want to learn more about other PostgreSQL clauses and basics, visit our Postgres basics section.