获取每个 Group By SQL 的最新记录 [英] Get the latest records per Group By SQL

问题描述

我有下表:

-----------------------------------------------------------
ID     oDate         oName     oItem    oQty    oRemarks
-----------------------------------------------------------
1      2016-01-01    A         001      2       
2      2016-01-01    A         002      1       test
3      2016-01-01    B         001      3  
4      2016-01-02    B         001      2
5      2016-01-02    C         001      2
6      2016-01-03    B         002      1
7      2016-01-03    B         001      4
       ff.

我想获取每个名字的最新记录.所以结果应该是这样的:

I want to get the latest record for each name. So the result should be like this:

-----------------------------------------------------------
oDate         oName     oItem    oQty    oRemarks
-----------------------------------------------------------
2016-01-01    A         001      2       
2016-01-01    A         002      1       test
2016-01-02    C         001      2
2016-01-03    B         002      1
2016-01-03    B         001      4
ff.

有谁知道如何得到这个结果?

Does anyone know how to do get this result?

推荐答案

rank window 子句允许您根据某些分区对行进行排名，然后您可以选择顶部的行:

The rank window clause allows you to, well, rank rows according to some partitioning, and then you could just select the top ones:

SELECT oDate, oName, oItem, oQty, oRemarks
FROM   (SELECT oDate, oName, oItem, oQty, oRemarks,
               RANK() OVER (PARTITION BY oName ORDER BY oDate DESC) AS rk
        FROM   my_table) t
WHERE  rk = 1

这篇关于获取每个 Group By SQL 的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！