Published at Jul 4, 2024
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.
The basic syntax of the ORDER BY clause is as follows:
SELECT column1, column2, …
FROM table
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
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');
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.
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.