选择最近的条目 [英] select the most recent entry

问题描述

我有下表:

LOCATION_ID, PERSON_ID, DATE
3, 65, 2016-06-03
7, 23, 2016-10-28
3, 23, 2016-08-05
5, 65, 2016-07-14

我想在PL/SQL中建立一个选择查询，以选择每个person_id具有最新 location_id的记录.对于以上示例，所需结果应为:

I want to build a select query in PL/SQL to select the records with the most recent location_id per person_id. For the above sample, the desired result should be:

LOCATION_ID, PERSON_ID, DATE
5, 65, 2016-07-14
7, 23, 2016-10-28

(DATE表示为"YYYY-MM-DD")

(DATE expressed as 'YYYY-MM-DD')

谢谢！

推荐答案

其他建议是正确的，但是当您使用

The other proposals are correct but the most compact and fastest solution is most likely when you use FIRST_VALUE and LAST_VALUE Analytic Functions

SELECT DISTINCT
   FIRST_VALUE(LOCATION_ID) OVER (PARTITION BY PERSON_ID ORDER BY THE_DATE 
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LOCATION_ID, 
   PERSON_ID, 
   MAX(THE_DATE) OVER (PARTITION BY PERSON_ID) AS LAST_DATE
FROM YOUR_TABLE;

其他人更喜欢

SELECT 
   MAX(LOCATION_ID) KEEP (DENSE_RANK FIRST ORDER BY DATE) as LOCATION, 
   PERSON_ID, 
   MAX(DATE) as LAST_DATE
FROM YOUR_TABLE
GROUP BY PERSON_ID;

它的作用相同，但是我对这个子句不太熟悉.请参见 aggregate_function KEEP

which does the same, but I am not so familiar with this clause. See aggregate_function KEEP

这篇关于选择最近的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！