Winning query?


select *
  from leaguearchive
 where (orgid, userid, archivedate) in
       (select orgid, userid, max(archivedate)
          from leaguearchive
         group by orgid, userid, trunc(archivedate, 'dd') )



Hello there,

I have an archive tabled that is populated with new data every 15 minutes from our servers, it looks something like this.

Orgid NUMBER
UserId NUMBER
ArchiveDate Datetime
Resources NUMBER


MY QUESTION IS

I want to get the latest archived row each (OrgId,UserId) for each day. I've been using the below query to get the corrent org/user/archivedate, but I would like to get the corresponding Resources value to go with the row.

Is there a good way of doing this?


select orgid, userid MAX(archivedate) as archivedate from leaguearchive
group by trunc(archivedate, 'dd'), orgid, userid