postgresql - 从保留顺序的内部查询中选择有限的字段集

Question

我有一个 SQL 查询，它涉及与 ORDER BY 子句的一对多关系:

SELECT 
  s0_.id,
  s0_.created_at,
  s5_.sort_order
FROM 
  surveys_submits s0_
  INNER JOIN surveys_answers s3_ ON s0_.id = s3_.submit_id
  INNER JOIN surveys_questions s4_ ON s3_.question_id = s4_.id 
  INNER JOIN surveys_questions_references s5_ ON s4_.id = s5_.question_id 
ORDER BY 
  s0_.created_at DESC, 
  s5_.sort_order ASC

此查询返回以下结果:

id  | created_at          | sort_order
----+---------------------+-----------
218 | 2014-03-18 12:21:09 | 1
218 | 2014-03-18 12:21:09 | 2
218 | 2014-03-18 12:21:09 | 3
218 | 2014-03-18 12:21:09 | 4
218 | 2014-03-18 12:21:09 | 5
217 | 2014-03-18 12:20:57 | 1
217 | 2014-03-18 12:20:57 | 2
217 | 2014-03-18 12:20:57 | 3

             ...

214 | 2014-03-18 12:18:01 | 4
214 | 2014-03-18 12:18:01 | 5
213 | 2014-03-18 12:17:48 | 1
213 | 2014-03-18 12:17:48 | 2
213 | 2014-03-18 12:17:48 | 3
213 | 2014-03-18 12:17:48 | 4
213 | 2014-03-18 12:17:48 | 5

现在，我需要修改此查询，以从头开始以保留顺序返回前 25 个不同的 ID。

我试过这样的:

SELECT DISTINCT id
FROM (
   SELECT ... ORDER BY ...
) inner_query
ORDER BY created_at DESC, sort_order ASC
LIMIT 25 OFFSET 0;

但是显然不行:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 16:       created_at DESC, 
               ^
********** Error **********

...而且我无法将 created_at 和 sort_order 列添加到 SELECT 子句中，因为它会导致重复的 id，就像第一个查询。

Answer 1

select *
from (
    SELECT distinct on (s0_.id)
        s0_.id,
        s0_.created_at,
        s5_.sort_order
    FROM 
        surveys_submits s0_
        INNER JOIN surveys_answers s3_ ON s0_.id = s3_.submit_id
        INNER JOIN surveys_questions s4_ ON s3_.question_id = s4_.id 
        INNER JOIN surveys_questions_references s5_ ON s4_.id = s5_.question_id 
    ORDER BY
        s0_.id,
        s0_.created_at DESC, 
        s5_.sort_order ASC
) s
order by
    created_at desc,
    sort_order ASC
limit 25

From the manual

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.