how to use the ORDER BY statement to sort query results - AnalyticDB for MySQL

This topic describes how to use the ORDER BY clause to sort query results.

Syntax

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

Parameter	Required	Description
`expression`	Yes	The fields by which to sort the query results. Valid values: The name of a field. For example, a value of `device` indicates that you want to sort the query results based on the `device` column. The ordinal number of a field. Fields are sorted from left to right. Ordinal numbers start from 1. For example, a value of `4` indicates that you want to sort the query results based on the fourth column.
`[ ASC \| DESC ]`	No	The order by which 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 every field. For example, `ORDER BY 2 ASC, 4 DESC` indicates that you want to sort the query results in ascending order based on the second column and 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 in 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