sql - postgresql - 如何获得一行的最小值

Question

我有包含此列的表 (t_image)

 datacd   | imagecode | indexdate 
----------------------------------
    A     |    1      |  20170213
    A     |    2      |  20170213
    A     |    3      |  20170214
    B     |    4      |  20170201
    B     |    5      |  20170202

想要的结果是这样的

    datacd   | imagecode | indexdate 
    ----------------------------------
        A    |    1      |  20170213
        B    |    4      |  20170201

在上表中，我想为每个具有最小索引日期的datacd检索1行

这是我的查询，但结果返回 datacd A

的 2 行

select *
from (
   select datacd, min(indexdate) as indexdate
   from t_image
   group by datacd
) as t1 inner join t_image as t2 on t2.datacd = t1.datacd and t2.indexdate = t1.indexdate;

Answer 1

Postgres 专有的 distinct on () 运算符通常是 greatest-n-per-group 最快的解决方案查询:

select distinct on (datacd) *
from t_image
order by datacd, indexdate;