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 to be sorted. Valid values:
  • The name of the output column. For example, device indicates that the query results are sorted by the device column.
  • The ordinal number of the output column. Query results are sorted from left to right. Ordinal numbers start from 1. The ordinal number of the output column indicates the position of the column in the query results. For example, 4 indicates that the query results are sorted by the fourth column.
[ ASC | DESC ] No The order in which query results are sorted. Valid values:
  • ASC: ascending order
  • DESC: descending order
Note
  • By default, if this parameter is not specified, the query results are sorted in ascending order.
  • If multiple fields are specified by the expression parameter, you must specify the sorting order for every field. For example, the ORDER BY 2 ASC, 4 DESC statement indicates that the query results are sorted in ascending order by the second column and in descending order by the fourth column.
[ LIMIT count] The number of rows to be returned in the query results.

If this parameter is not specified, all rows in the query results are returned.

Examples

  • The following statement collects statistics about the number of devices sold in different cities, and sorts them by device name and number of devices sold:
    SELECT os ,device,city,COUNT(*) AS num FROM requests GROUP BY os,device,city ORDER BY num,device;
    The following content is returned:
    os      |device |city         |num
    --------+-------+-------------+---
    windows |PC     |Hangzhou     |1
    windows |PC     |Shenzhen     |1
    windows |PC     |Shanghai     |1
    linux   |PC     |Shanghai     |1
    windows |PC     |Shijiazhuang |2
    linux   |PC     |Beijing      |2
    windows |PC     |Beijing      |4
    linux   |Phone  |Hangzhou     |1
    ios     |Phone  |Zhangjiakou  |1
    windows |Phone  |Shenzhen     |1
    linux   |Phone  |Beijing      |2
    ios     |Phone  |Shijiazhuang |2
    windows |Phone  |Shijiazhuang |2
  • The following statement collects statistics about the cities that rank top 5 in the number of devices sold, and sorts them in descending order by device name and in ascending order by number of devices sold:
    SELECT os,device,city,COUNT(*) AS num FROM requests GROUP BY os,device,city ORDER BY 2 DESC,4 ASC LIMIT 5;
    The following content is returned:
    os     |device |city        |num
    -------+-------+------------+----
    windows|Phone  |Shenzhen    |1
    linux  |Phone  |Hangzhou    |1
    ios    |Phone  |Zhangjiakou |1
    linux  |Phone  |Beijing     |2
    ios    |Phone  |Shijiazhuang|2