每个按群组获取最新记录(Get the latest records per 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.

我想获得每个名字的最新记录。 所以结果应该是这样的：

----------------------------------------------------------- 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.

有谁知道如何得到这个结果？ 谢谢。

I have the following table:

----------------------------------------------------------- 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? Thank you.

最满意答案

rank窗口子句允许您根据某些分区对行进行排名，然后您可以选择最上面的那些：

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

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