CODEWITHSUNDEEP
sql-servert-sql
CuriousBeing
CuriousBeing

Reputation: 1632

Most Recent Data

I have a schema that looks like this. enter image description here

Using the recordings table , query the most recent data in the database for each entityid/accid/month combination (Main Table is the main table)

Upvotes: 0

Views: 48

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query.

select * from 
    (select m.*, r.*, e.*, a.*, row_number() over(partition by a.AcctID, e.EntID, Month order by r.TimeStamp desc) row_num
        from maintable m 
        inner join racordings r on m.LogID = r.LogID
        inner join Entities e on e.EntID = m.EntID
        inner join AccInfo a on a.AcctID = m.AcctID
    ) t1 
where t1.row_num = 1

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use row_number()

  select * from 
    (
    select m.*, row_number() over(partition by acctid,entid,month order by timestamp desc) rn
    from maintable m join racordings r on m.logid=r.logid
    ) a where a.rn=1

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

use row_number()

select * from
(
    select *,row_number() over(partition by entid, accid, month order by timestamp desc) as rn 
    from recordings a inner join maintable b on a.logid=b.logid
)A where rn=1

Upvotes: 2

Related Questions