sql选择排序结果的前n个唯一行

Question

我查询得到 1 列字符串，结果示例:

NAME:
-----
SOF
OTP
OTP
OTP
SOF
VIL
OTP
SOF
GGG

我希望能够获得 SOF、OTP、VIL - 前 3 个独特的顶部，
我尝试使用 DISTINCT 和 GROUP BY，但它不起作用，排序已损坏..

构建此结果的查询是:

SELECT DISTINCT d.adst 
FROM   (SELECT a.date              adate, 
               b.date              bdate, 
               a.price + b.price   total, 
               ( b.date - a.date ) days, 
               a.dst               adst 
        FROM   flights a 
               JOIN flights b 
                 ON a.dst = b.dst 
        ORDER  BY total) d 

我有包含详细信息的“航类”表，我需要获取 3 (=n) 个最便宜的目的地。

谢谢

Answer 1

这可以使用窗口函数轻松完成:

select *
from (
       SELECT a.date as adate, 
              b.date as bdate, 
              a.price + b.price as total, 
              dense_rank() over (order by a.price + b.price) as rnk,
              b.date - a.date as days, 
              a.dst  as adst 
        FROM  flights a 
          JOIN flights b ON a.dst = b.dst 
) t
where rnk <= 3
order by rnk;

关于窗口函数的更多细节可以在手册中找到:
http://www.postgresql.org/docs/current/static/tutorial-window.html