Use the ORDER BY clause to sort query results in descending or ascending order-AnalyticDB(AnalyticDB)-阿里云帮助中心

Sorts query results by one or more columns. Use LIMIT with ORDER BY to return only the top N rows after sorting.

Syntax

ORDER BY expression [ASC | DESC] [, expression [ASC | DESC] ...]
[LIMIT count]

Parameters

Parameter	Required	Description
`expression`	Yes	The column to sort by. Accepts a column name (for example, `device`) or the column's ordinal number in the result set, counting from left to right starting at 1 (for example, `4` refers to the fourth column).
`ASC \| DESC`	No	The sort direction. `ASC` sorts in ascending order (default). `DESC` sorts in descending order. When sorting by multiple columns, specify the direction for each column independently.
`LIMIT count`	No	The maximum number of rows to return. If omitted, all rows are returned.

Usage notes

Sorting by multiple columns

Specify multiple sort expressions separated by commas. Each expression can have its own direction. For example, ORDER BY 2 ASC, 4 DESC sorts ascending by the second column, then descending by the fourth.

Performance with LIMIT

Queries that use ORDER BY ... LIMIT can be slow when the sorted columns have no clustered index. A regular index on those columns cannot be used for this query type. To improve performance:

Create a clustered index on the columns in the ORDER BY clause.

Important
A clustered index defaults to ascending order. If your query sorts in descending order, the clustered index is not used.
Run a BUILD task after creating the index. Trigger a BUILD task manually or wait for the automatic BUILD task to run.

Examples

Sort by column name and column ordinal number

Query device sales by city, sorted by num (ascending) and then device (ascending):

SELECT os, device, city, COUNT(*) AS num
FROM requests
GROUP BY os, device, city
ORDER BY num, device;

Output:

os      | device | city         | num
--------+--------+--------------+-----
Linux   | PC     | Shanghai     | 1
windows | PC     | Shenzhen     | 1
windows | PC     | Shanghai     | 1
windows | PC     | Hangzhou     | 1
windows | Phone  | Shenzhen     | 1
Linux   | Phone  | Hangzhou     | 1
ios     | Phone  | Zhangjiakou  | 1
windows | PC     | Shijiazhuang | 2
Linux   | PC     | Beijing      | 2
ios     | Phone  | Shijiazhuang | 2
windows | Phone  | Shijiazhuang | 2
Linux   | Phone  | Beijing      | 2
windows | PC     | Beijing      | 4

Sort by column ordinal number with LIMIT

Query the top 5 cities that have the highest device sales, sorted descending by device name (second column) and ascending by device sales (fourth column):

SELECT os, device, city, COUNT(*) AS num
FROM requests
GROUP BY os, device, city
ORDER BY 2 DESC, 4 ASC
LIMIT 5;

Output:

os      | device | city         | num
--------+--------+--------------+-----
ios     | Phone  | Zhangjiakou  | 1
windows | Phone  | Shenzhen     | 1
Linux   | Phone  | Hangzhou     | 1
windows | Phone  | Shijiazhuang | 2
Linux   | Phone  | Beijing      | 2