SQL Server Query for latest date for each unique value

hs1ihplo 于 12个月前发布在其他

关注(0)|答案(2)|浏览(135)

I have atm table and replenishment table both have the same atm_id , atm.ATM_id = replenishment.atm_id .

So I want to get back every atm.title . atm.ATM_id with its last replenishment.rep_datetime .

Trying to find what did I miss. Thank you for your help

I tried this one

select distinct atm.ATM id, atm.title, cash added1, cash added2, cash added3, cash added4,rep_datetime
from replenishment
inner join atm on atm.ATM_id = replenishment.atm_id
where rep_datetime = (select max(rep_datetime) from replenishment)
order by rep_datetime DESC

Any ideas on this one noting that the result gets one record with the latest date for single value but what I want is to have all ids with their latest date.

Expecting all ids without duplications with their latest/newest date.

sql-server

来源：https://stackoverflow.com/questions/77685314/query-for-latest-date-for-each-unique-value

2条答案

按热度按时间

avkwfej41#

You can use a windowing function for this:

SELECT ATM_id, atm.title, cash_added1, cash_added2, cash_added3, cash_added4, rep_datetime
FROM (
    select atm.ATM_id, atm.title, rep_datetime
        , cash_added1, cash_added2, cash_added3, cash_added4
        , row_number() over (partition by atm.atm_id order by rep_datetime desc) rn
    from atm 
    inner join replenishment r on atm.ATM_id = r.atm_id
) t
WHERE rn = 1

赞(0）回复(0）举报 12个月前

pu82cl6c2#

select a.Atm_id, a.title, 
-- ...
r.repDate
from atm a
inner join
(select atm_id, max(rep_datetime) as repDate 
  from replenishment group by atm_id) r
on a.ATM id = r.atm_id
order by r.repDate DESC;

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Query for latest date for each unique value

2条答案

相关问题

热门标签

最新问答