Use the ORDER BY clause to sort query results in descending or ascending order - AnalyticDB

You can use the ORDER BY clause to sort query results based on specific fields. You can use the LIMIT clause together with the ORDER BY clause to specify the number of rows to be returned after being sorted. This topic describes the ORDER BY syntax and provides examples on how to use the ORDER BY clause.

Syntax

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

Parameter	Required	Description
`expression`	Required	The fields based on which you want to sort the query results. Valid values: The name of a field. For example, a value of `device` specifies that you want to sort the query results based on the `device` column. The ordinal number of a field in the query results. Fields are sorted from left to right. Ordinal numbers start from 1. For example, a value of `4` specifies that you want to sort the query results based on the fourth column.
`[ ASC \| DESC ]`	Optional	The order based on which you want to sort the query results. Valid values: `ASC`: ascending order (default). `DESC`: descending order. Note If you specify multiple fields for the `expression` parameter, you can specify the sorting order for each field. For example, `ORDER BY 2 ASC, 4 DESC` specifies that you want to sort the query results in ascending order based on the second column and then in descending order based on the fourth column.
`[ LIMIT count]`	Optional	The number of rows to be returned. If you leave this parameter empty, the system returns all rows of the query results.

Examples

Query device sales in different cities and sort the results by device name and device sales.

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

The following information is returned:

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

Query the top 5 cities that have the highest device sales and sort the results in descending order by device name and in ascending order by device sales.

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

The following information is returned:

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

FAQ

Why does a SQL statement that contains ORDER BY LIMIT take a long time to execute?

Reason: No clustered index is created for the columns included in the ORDER BY LIMIT clause. Even if a regular index is created for these columns, the regular index cannot be applied during the query.

Solutions:

Create a clustered index for the columns included in the ORDER BY LIMIT clause.
Important
If you do not specify a sorting method when you create a clustered index, the default sorting method is ascending order. In this case, if your query uses descending order, the clustered index still cannot be used.
Execute a BUILD task.
You can manually trigger a BUILD task or wait for the system to automatically trigger a BUILD task.