| 1 |
SQL Language Extension: ORDER BY clause can specify expressions and nulls placement |
| 2 |
|
| 3 |
Syntax |
| 4 |
======== |
| 5 |
|
| 6 |
SELECT ... FROM .... ORDER BY order_list ....; |
| 7 |
|
| 8 |
order_list : order_item [, order_list]; |
| 9 |
|
| 10 |
order_item : <expression> [order_direction] [nulls_placement] |
| 11 |
|
| 12 |
order_direction : ASC | DESC; |
| 13 |
|
| 14 |
nulls_placement : NULLS FIRST | NULLS LAST; |
| 15 |
|
| 16 |
The ORDER BY clause lets you specify any valid expressions to |
| 17 |
sort query results. If expression is consisted of a single number |
| 18 |
it is interpreted as column number. The nulls_placement clause |
| 19 |
controls ordering of nulls in result set. They can be sorted |
| 20 |
either above (NULLS FIRST) or below (NULLS LAST) of all other values. |
| 21 |
Behaviour when nulls_placement is unspecified is NULLS FIRST. |
| 22 |
|
| 23 |
Author: |
| 24 |
Nickolay Samofatov <[email protected]> |
| 25 |
|
| 26 |
|
| 27 |
N O T E S |
| 28 |
========= |
| 29 |
|
| 30 |
- If you override the default nulls placement, no index can be used |
| 31 |
for sorting. That is, no index will be used for an ASCENDING sort |
| 32 |
if NULLS LAST is specified, nor for a DESCENDING sort |
| 33 |
if NULLS FIRST is specified. |
| 34 |
- Results are undefined if you ask engine to sort results using |
| 35 |
non-deterministic UDF or stored procedure. |
| 36 |
- Amount of procedure invocations is undefined if you ask engine to |
| 37 |
sort results using UDF or stored procedure in any case even |
| 38 |
if you reference column calling procedure by number |
| 39 |
- You can use only numbers to reference columns to sort unions |
| 40 |
|
| 41 |
Examples |
| 42 |
======== |
| 43 |
|
| 44 |
|
| 45 |
A) |
| 46 |
SELECT * FROM MSG |
| 47 |
ORDER BY PROCESS_TIME DESC NULLS FIRST |
| 48 |
|
| 49 |
B) |
| 50 |
SELECT FIRST 10 * FROM DOCUMENT |
| 51 |
ORDER BY STRLEN(DESCRIPTION) DESC |
| 52 |
|
| 53 |
C) |
| 54 |
SELECT DOC_NUMBER, DOC_DATE FROM PAYORDER |
| 55 |
UNION ALL |
| 56 |
SELECT DOC_NUMBER, DOC_DATA FROM BUDGORDER |
| 57 |
ORDER BY 2 DESC NULLS LAST, 1 ASC NULLS FIRST |
| 58 |
|