sql - 如何根据多个字段选择最大值并按多个字段分组

Question

请协助完成以下工作。样本数据：

LOCATION    ITEMNO  TRANSDATE   DAYENDSEQ   ENTRYSEQ    LINENO  ANSWER
Area1       A       20180702    1           1           1       a
Area1       A       20180703    1           1           1       b
Area1       A       20180703    2           1           1       c
Area1       A       20180703    2           2           1       d
Area1       A       20180703    2           2           2       e
Area1       B       20180703    1           1           1       f
Area1       C       20180703    1           1           2       g
Area1       C       20180703    1           1           1       h
Area2       A       20180703    1           2           1       i
Area2       A       20180703    1           1           4       j
Area2       A       20180703    1           1           5       k

返回每个位置的每个项目的 ANSWER 值，其中 TRANSDATE、DAYENDSEQ、ENTRYSEQ 和 LINENO（按此顺序）的组合最大。

LOCATION    ITEMNO  TRANSDATE   DAYENDSEQ   ENTRYSEQ    LINENO  ANSWER
Area1       A       20180703    2           2           2       e
Area1       B       20180703    1           1           1       f
Area1       C       20180703    1           1           2       g
Area2       A       20180703    1           2           1       i

标签： sqlgroup-bymaxmultiple-columns

Answer 1

使用 ANSI 标准row_number()函数：

select t.*
from (select t.*,
             row_number() over (partition by location, itemno 
                                order by transdate desc, dayendseq desc, entryseq desc, lineno desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;