ORDER BY Clauses
January 3, 2025About 4 min
ORDER BY Clauses
The ORDER BY
clause is used to sort the result set of a query at its final stage based on specified sorting conditions.
1. Syntax Overview
ORDER BY sortItem (',' sortItem)*
sortItem
: expression (ASC | DESC)? (NULLS (FIRST | LAST))?
;
1.1 ORDER BY Clauses
- Allows sorting query result rows based on specified conditions in ascending order (
ASC
) or descending order (DESC
). - Provides control over the position of
NULL
values, enabling users to specify whetherNULL
values appear at the beginning (NULLS FIRST
) or the end (NULLS LAST
). - By default, sorting is applied as
ASC NULLS LAST
, meaning values are sorted in ascending order andNULL
values are placed at the end. Users can manually specify other parameters to override the default behavior. - The
ORDER BY
clause is executed before theLIMIT
orOFFSET
clauses.
2. Example Data
The Example Data pagepage provides SQL statements to construct table schemas and insert data. By downloading and executing these statements in the IoTDB CLI, you can import the data into IoTDB. This data can be used to test and run the example SQL queries included in this documentation, allowing you to reproduce the described results.
Example 1: Query data from the past hour in descending order of time
SELECT *
FROM table1
WHERE time >= 2024-11-27 00:00:00 and time <= 2024-11-29 00:00:00
ORDER BY time DESC;
Results:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| modifytime|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-28T11:00:00.000+08:00| 上海| 3001| 100| C| 90| 88.0| 45.1| true|2024-11-28T11:00:12.000+08:00|
|2024-11-28T10:00:00.000+08:00| 上海| 3001| 100| C| 90| 85.0| 35.2| null|2024-11-28T10:00:11.000+08:00|
|2024-11-28T09:00:00.000+08:00| 上海| 3001| 100| C| 90| null| 40.9| true| null|
|2024-11-28T08:00:00.000+08:00| 上海| 3001| 100| C| 90| 85.0| null| null|2024-11-28T08:00:09.000+08:00|
|2024-11-27T16:44:00.000+08:00| 北京| 1001| 101| B| 180| null| null| false|2024-11-26T16:37:08.000+08:00|
|2024-11-27T16:43:00.000+08:00| 北京| 1001| 101| B| 180| null| null| false| null|
|2024-11-27T16:42:00.000+08:00| 北京| 1001| 101| B| 180| null| 35.2| false| null|
|2024-11-27T16:41:00.000+08:00| 北京| 1001| 101| B| 180| 85.0| null| null|2024-11-26T16:37:04.000+08:00|
|2024-11-27T16:40:00.000+08:00| 北京| 1001| 101| B| 180| 85.0| null| null|2024-11-26T16:37:03.000+08:00|
|2024-11-27T16:39:00.000+08:00| 北京| 1001| 101| B| 180| 85.0| 35.3| null| null|
|2024-11-27T16:38:00.000+08:00| 北京| 1001| 101| B| 180| null| 35.1| true|2024-11-26T16:37:01.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 11
It costs 0.148s
Example 2: Query data sorted by device_id in ascending order and time in descending order, with NULL temperatures displayed first
SELECT *
FROM table1
WHERE time >= 2024-11-27 00:00:00 and time <= 2024-11-29 00:00:00
ORDER BY temperature NULLS FIRST, time DESC;
Results:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| modifytime|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-28T09:00:00.000+08:00| 上海| 3001| 100| C| 90| null| 40.9| true| null|
|2024-11-27T16:44:00.000+08:00| 北京| 1001| 101| B| 180| null| null| false|2024-11-26T16:37:08.000+08:00|
|2024-11-27T16:43:00.000+08:00| 北京| 1001| 101| B| 180| null| null| false| null|
|2024-11-27T16:42:00.000+08:00| 北京| 1001| 101| B| 180| null| 35.2| false| null|
|2024-11-27T16:38:00.000+08:00| 北京| 1001| 101| B| 180| null| 35.1| true|2024-11-26T16:37:01.000+08:00|
|2024-11-28T10:00:00.000+08:00| 上海| 3001| 100| C| 90| 85.0| 35.2| null|2024-11-28T10:00:11.000+08:00|
|2024-11-28T08:00:00.000+08:00| 上海| 3001| 100| C| 90| 85.0| null| null|2024-11-28T08:00:09.000+08:00|
|2024-11-27T16:41:00.000+08:00| 北京| 1001| 101| B| 180| 85.0| null| null|2024-11-26T16:37:04.000+08:00|
|2024-11-27T16:40:00.000+08:00| 北京| 1001| 101| B| 180| 85.0| null| null|2024-11-26T16:37:03.000+08:00|
|2024-11-27T16:39:00.000+08:00| 北京| 1001| 101| B| 180| 85.0| 35.3| null| null|
|2024-11-28T11:00:00.000+08:00| 上海| 3001| 100| C| 90| 88.0| 45.1| true|2024-11-28T11:00:12.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 11
It costs 0.060s
Example 3: Query the top 10 rows with the highest temperature values
SELECT *
FROM table1
ORDER BY temperature DESC NULLS LAST
LIMIT 10;
Results:
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
| time|region|plant_id|device_id|model_id|maintenance|temperature|humidity|status| modifytime|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
|2024-11-30T09:30:00.000+08:00| 上海| 3002| 101| F| 360| 90.0| 35.2| true| null|
|2024-11-26T13:38:00.000+08:00| 北京| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:38:25.000+08:00|
|2024-11-30T14:30:00.000+08:00| 上海| 3002| 101| F| 360| 90.0| 34.8| true|2024-11-30T14:30:17.000+08:00|
|2024-11-26T13:37:00.000+08:00| 北京| 1001| 100| A| 180| 90.0| 35.1| true|2024-11-26T13:37:34.000+08:00|
|2024-11-29T18:30:00.000+08:00| 上海| 3002| 100| E| 180| 90.0| 35.4| true|2024-11-29T18:30:15.000+08:00|
|2024-11-28T11:00:00.000+08:00| 上海| 3001| 100| C| 90| 88.0| 45.1| true|2024-11-28T11:00:12.000+08:00|
|2024-11-28T10:00:00.000+08:00| 上海| 3001| 100| C| 90| 85.0| 35.2| null|2024-11-28T10:00:11.000+08:00|
|2024-11-28T08:00:00.000+08:00| 上海| 3001| 100| C| 90| 85.0| null| null|2024-11-28T08:00:09.000+08:00|
|2024-11-29T10:00:00.000+08:00| 上海| 3001| 101| D| 360| 85.0| null| null|2024-11-29T10:00:13.000+08:00|
|2024-11-27T16:40:00.000+08:00| 北京| 1001| 101| B| 180| 85.0| null| null|2024-11-26T16:37:03.000+08:00|
+-----------------------------+------+--------+---------+--------+-----------+-----------+--------+------+-----------------------------+
Total line number = 10
It costs 0.069s