查找每个帐户的最新合同 [英] Finding the most recent contract for each account

问题描述

我正在寻找一个帐户的最新条目，并且只为该帐户提取该值.我正在使用Oracle数据库.这是一个示例:

I am looking to find the most recent entry for an account and only pulling that value for the account. I am working with an oracle database. Here is an example:

account date    value
123456  4/20/2017   5
123456  1/20/2017   10
987654  2/5/2018    15
987654  12/31/2017  20
456789  4/27/2018   50
456789  1/24/2018   60

我想将2017年4/20/2017的值拉为123456，将2/5/2018的值拉为987654，将4/27/2018的值拉为456789.

I would want to pull the 4/20/2017 value for 123456, the 2/5/2018 value for 987654, and the 4/27/2018 value for 456789.

谢谢您的帮助

推荐答案

使用ROW_NUMBER:

SELECT account, "date", "value"
FROM
(SELECT 
  account, "date", "value",
  ROW_NUMBER() OVER (PARTITION BY account ORDER BY "date" DESC) rn
 FROM yourTable
) t
WHERE rn=1;

请注意，如果只希望每个帐户的最长日期，那么@NiVeR现在删除的答案就足够了.如果我们还需要该值，或者通常需要每条记录中的其他列，那么我们将不得不执行额外的联接，或者像我上面那样使用行号.

Note that the now deleted answer given by @NiVeR would suffice if we only wanted the max date for each account. If we also need the value, or in general other columns from each record, then we would have to do an additional join, or use row number as I have done above.

请参见关于SQL Fiddle的演示.

这篇关于查找每个帐户的最新合同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！