ORDER BY Clause: Sorting Query Output

The query returns results matching the criteria unsorted — i.e., in the order they've been found in the table. To produce sorted output — alphabetically or numerically — you would use an ORDER BY clause. The functionality of this clause is identical across all "big-three" databases.

The following query sorts the output by the customer name alphabetically in ascending order:

SQL> SELECT cust_name_s,              cust_alias_s,              cust_status_s FROM        customer ORDER BY    cust_name_s;     cust_name_s                      cust_alias_s    cust_status_s  -------------------------------- --------------- -------------  BOSWELL DESIGNS CORP.            BWSO71471       Y BURNETTE WILLIAM CORP.           BNRU71477       Y CARLTONBATES COMPANY             CLRA71445       Y CHGO SWITCHBOARD INC.            COGH71478       N COFFMAN DOUGLAS                  CFFO71482       Y . . .  WILE ELECTRONICS INC.            ISCC71419       Y WILE SEAL CORP.                  MNGA71396       Y     37 rows selected.

The results could be sorted in either ascending or descending order. To sort in descending order, you must specify keyword DESC after the column name; to sort in ascending order you may use ASC keyword (or omit it altogether, as it is done in the above query, since ascending is the default sorting order).

The column used in the ORDER BY clause does not have to appear in the SELECT clause, though it must be present in the table:

SELECT   cust_name_s,           cust_alias_s FROM     customer ORDER BY cust_status_s

In this query, results are sorted by CUST_STATUS_S — a column that is not among those selected for display.

It is possible to sort by more than one column at the same time, though results might not be as obvious. The precedence of the columns in the ORDER BY clause is of importance here: First results are sorted by the values of the first column, then — within the order established by the first column — the results will be sorted by the second column values.

It is even possible to specify different orders — ascending or descending for the different columns listed in the ORDER BY clause. The following example orders the records from the PRODUCT table first by the price in ascending order, then by the net weight — in descending order for each price value:

SELECT   prod_id_n,           prod_price_n,           prod_netwght_n  FROM     product ORDER BY prod_price_n ASC,           prod_netwght_n DESC     PROD_ID_N   prod_price_n prod_netwght_n  ----------- ------------ --------------  4000        11.80        15.194 . . .       . . .        . . . 990         18.24        21.226 4964        23.10        18.480 4761        23.10        10.032 2871        26.82        34.552 4906        27.00        22.000 1880        33.28        38.391     10 rows selected

Among the results returned by the query (which are sorted by the price in ascending order) there are two products (PROD_ID_N = 4964 and PROD_ID_N = 4761) that happen to have an identical price — $23.10. You can see that the values of the column PROD_NETWGHT_N for these products IDs are sorted in descending order.

Note 

If any of the column values contain NULLs, they would appear last on the ascending list, as NULLs by definition have ASCII number greater than any printable character.

Both Oracle and Microsoft SQL Server 2000 allow using ordinal numbers in the ORDER BY clause — i.e., instead of the column name you may use a number referring to the order in which this column appears in the SELECT part of the query. Following these rules, the above query could be rewritten as

SELECT   prod_id_n,           prod_price_n,           prod_netwght_n  FROM     product ORDER BY 2 ASC,           3 DESC

When ordinals are used instead of the column names in the ORDER BY clause, all these columns must appear in the SELECT statement (compare with the previous paragraph).

The ORDER BY clause is commonly used in conjunction with the GROUP BY expression, and the ordering could be performed by a computed column. The next query demonstrates this; it also uses a computed aliased AVG value for sorting purposes:

SELECT   ordline_ordhdrid_fn,          AVG(ordline_ordqty_n) AS AVG_QTY_PER_ORDER  FROM     order_line  GROUP BY ordline_ordhdrid_fn  ORDER BY AVG_QTY_PER_ORDER DESC ORDLINE_ORDHDRID_FN AVG_QTY_PER_ORDER ------------------- -----------------               30610               700               30619               700               30650               700               ...               30668             157.8               30622             145.2               30662             145.2     51 rows selected.

Note 

The GROUP BY and ORDER BY clauses have certain similarities in both syntax and usage. They both are dealing with the ordering of data, and sometimes it is possible to use a GROUP BY clause in place of an ORDER BY clause. (That might be useful; for example, in the view creation process when the ORDER BY clause is illegal.) However, GROUP BY used for this purpose would have some disadvantages: You must list all nonaggregate columns listed in the SELECT clause in the GROUP BY clause, and you cannot use numbers to designate columns.