Debian Codegraph @

doc/sql.extensions/README.order_by_expressions_nulls

raw
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