select_distinct_on.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 67 行

--
-- SELECT_DISTINCT_ON
--
SELECT DISTINCT ON (string4) string4, two, ten
   FROM tmp
   ORDER BY string4 using <, two using >, ten using <;
 string4 | two | ten 
---------+-----+-----
 AAAAxx  |   1 |   1
 HHHHxx  |   1 |   1
 OOOOxx  |   1 |   1
 VVVVxx  |   1 |   1
(4 rows)

-- this will fail due to conflict of ordering requirements
SELECT DISTINCT ON (string4, ten) string4, two, ten
   FROM tmp
   ORDER BY string4 using <, two using <, ten using <;
ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
SELECT DISTINCT ON (string4, ten) string4, ten, two
   FROM tmp
   ORDER BY string4 using <, ten using >, two using <;
 string4 | ten | two 
---------+-----+-----
 AAAAxx  |   9 |   1
 AAAAxx  |   8 |   0
 AAAAxx  |   7 |   1
 AAAAxx  |   6 |   0
 AAAAxx  |   5 |   1
 AAAAxx  |   4 |   0
 AAAAxx  |   3 |   1
 AAAAxx  |   2 |   0
 AAAAxx  |   1 |   1
 AAAAxx  |   0 |   0
 HHHHxx  |   9 |   1
 HHHHxx  |   8 |   0
 HHHHxx  |   7 |   1
 HHHHxx  |   6 |   0
 HHHHxx  |   5 |   1
 HHHHxx  |   4 |   0
 HHHHxx  |   3 |   1
 HHHHxx  |   2 |   0
 HHHHxx  |   1 |   1
 HHHHxx  |   0 |   0
 OOOOxx  |   9 |   1
 OOOOxx  |   8 |   0
 OOOOxx  |   7 |   1
 OOOOxx  |   6 |   0
 OOOOxx  |   5 |   1
 OOOOxx  |   4 |   0
 OOOOxx  |   3 |   1
 OOOOxx  |   2 |   0
 OOOOxx  |   1 |   1
 OOOOxx  |   0 |   0
 VVVVxx  |   9 |   1
 VVVVxx  |   8 |   0
 VVVVxx  |   7 |   1
 VVVVxx  |   6 |   0
 VVVVxx  |   5 |   1
 VVVVxx  |   4 |   0
 VVVVxx  |   3 |   1
 VVVVxx  |   2 |   0
 VVVVxx  |   1 |   1
 VVVVxx  |   0 |   0
(40 rows)