1-- 2-- SELECT_DISTINCT_ON 3-- 4SELECT DISTINCT ON (string4) string4, two, ten 5 FROM tmp 6 ORDER BY string4 using <, two using >, ten using <; 7 string4 | two | ten 8---------+-----+----- 9 AAAAxx | 1 | 1 10 HHHHxx | 1 | 1 11 OOOOxx | 1 | 1 12 VVVVxx | 1 | 1 13(4 rows) 14 15-- this will fail due to conflict of ordering requirements 16SELECT DISTINCT ON (string4, ten) string4, two, ten 17 FROM tmp 18 ORDER BY string4 using <, two using <, ten using <; 19ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions 20LINE 1: SELECT DISTINCT ON (string4, ten) string4, two, ten 21 ^ 22SELECT DISTINCT ON (string4, ten) string4, ten, two 23 FROM tmp 24 ORDER BY string4 using <, ten using >, two using <; 25 string4 | ten | two 26---------+-----+----- 27 AAAAxx | 9 | 1 28 AAAAxx | 8 | 0 29 AAAAxx | 7 | 1 30 AAAAxx | 6 | 0 31 AAAAxx | 5 | 1 32 AAAAxx | 4 | 0 33 AAAAxx | 3 | 1 34 AAAAxx | 2 | 0 35 AAAAxx | 1 | 1 36 AAAAxx | 0 | 0 37 HHHHxx | 9 | 1 38 HHHHxx | 8 | 0 39 HHHHxx | 7 | 1 40 HHHHxx | 6 | 0 41 HHHHxx | 5 | 1 42 HHHHxx | 4 | 0 43 HHHHxx | 3 | 1 44 HHHHxx | 2 | 0 45 HHHHxx | 1 | 1 46 HHHHxx | 0 | 0 47 OOOOxx | 9 | 1 48 OOOOxx | 8 | 0 49 OOOOxx | 7 | 1 50 OOOOxx | 6 | 0 51 OOOOxx | 5 | 1 52 OOOOxx | 4 | 0 53 OOOOxx | 3 | 1 54 OOOOxx | 2 | 0 55 OOOOxx | 1 | 1 56 OOOOxx | 0 | 0 57 VVVVxx | 9 | 1 58 VVVVxx | 8 | 0 59 VVVVxx | 7 | 1 60 VVVVxx | 6 | 0 61 VVVVxx | 5 | 1 62 VVVVxx | 4 | 0 63 VVVVxx | 3 | 1 64 VVVVxx | 2 | 0 65 VVVVxx | 1 | 1 66 VVVVxx | 0 | 0 67(40 rows) 68 69-- bug #5049: early 8.4.x chokes on volatile DISTINCT ON clauses 70select distinct on (1) floor(random()) as r, f1 from int4_tbl order by 1,2; 71 r | f1 72---+------------- 73 0 | -2147483647 74(1 row) 75 76