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. |