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`	Yes	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 ]`	No	The order based on which you want to sort the query results. Valid values: `ASC` (default): ascending order. `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]`	No	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