sql - 在 PostgreSQL 中使用 group by 时如何获取最后一条记录

Question

这是我的表“AuctionDetails”

以下选择:

select string_agg("AuctionNO",',' ) as  "AuctionNO"
      ,sum("QuntityInAuction" ) as "QuntityInAuction"
      ,"AmmanatPattiID"
      ,"EntryPassDetailsId" 
      ,"BrokerID"
      ,"TraderID"
      ,"IsSold"
      ,"IsActive"
      ,"IsExit"
      ,"IsNew"
      ,"CreationDate"
from "AuctionDetails"
group by "AmmanatPattiID"
        ,"EntryPassDetailsId"  
        ,"TraderID"
        ,"IsSold"
        ,"IsActive"
        ,"IsExit"
        ,"IsNew"
        ,"BrokerID"
        ,"CreationDate"

给我这个结果:

但我需要这样的记录

AuctionNo                                QunatityInAuction  AmmanatpattiID  EntryPassDetailID  BrokerID  Trader ID  IsSold  ISActive  ISExit  IsNew  CreationDate
AU8797897,AU8797886,AU596220196F37379    1050               -1               228,229            42         42         f         t       f      t      2013-10-10

最后我需要交易者和经纪人的最新条目，在我们的例子中是“42”，数量总和，以及拍卖编号的串联......

Answer 1

Postgres wiki 描述了如何定义您自己的 FIRST 和 LAST 聚合函数。例如:

-- Create a function that always returns the last non-NULL item
CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $2;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.LAST (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);

页面在这里:https://wiki.postgresql.org/wiki/First/last_(aggregate)