1

Take a look at this sqlfiddle. I have a table that contains history of records for another table(irrelevant, foreign key included). Each history item will contain a status and time fields. I want to write a select statement that would give me a list of unique(by foreign key) histories with the latest status.

From other searches I think what I need is

SELECT MAX(created) as created, originalMsgId 
from ReplayHistory 
group by originalMsgId
created             Id
------------------------
2018-06-22 19:18:43 11
2018-06-22 09:28:43 12

but it doesn't return the column I need; status.

The query I am using is

SELECT MAX(created) as created, originalMsgId, status 
from ReplayHistory 
group by originalMsgId, status

which returns me

created              Id  status
----------------------------------
2018-06-22 19:18:43  11  Finished
2018-06-22 19:17:43  11  InProgress
2018-06-22 09:28:43  12  InProgress
2018-06-22 19:16:43  11  NotStarted
2018-06-22 09:27:43  12  NotStarted

I know that this is happening because of group by status, but if I need it in select then I have to keep it in group by.

2

This is a good use for a window function, row_number()

select 
   x.created, 
   x.OriginalMsgId, 
   x.Status
from (SELECT 
         RN = row_number() over (partition by r.originalMsgId order by Created desc), 
         r.originalMsgId, 
         r.status, 
         created 
      from ReplayHistory r) x
where x.RN = 1

Another way is using MAX with a derived table.

SELECT 
   r.originalMsgId, 
   r.status, 
   r.created 
from ReplayHistory r
inner join
    (select originalMsgId, max(created) dt from ReplayHistory group by originalMsgId) x on
    x.dt = r.created 
    and x.originalMsgId = r.originalMsgId

Notes on performance

  • the second query might not work if you have multiple rows with the same value in 'created' column – Taha Rehman Siddiqui Jun 25 '18 at 19:39
  • Correct @TahaRehmanSiddiqui but i wouldn't have expected the exact time down to the second for a given ID. This means that an ID would have to have duplicate data or change status twice in a second (presumably). If that's a possibility, then you are correct – scsimon Jun 25 '18 at 19:40
  • Youre right, might not happen in real world. I have duplicated data in my table that I put for testing. – Taha Rehman Siddiqui Jun 25 '18 at 19:43
  • By the way? Any idea if the query would run in mysql and oracle as well? – Taha Rehman Siddiqui Jun 25 '18 at 19:44
  • I'm not versed on those platforms, but the second one should work in MySQL (not sure about Oracle but I'd think so). I don't think MySQL has row_number, but here's a post on that *. Oracle does however , *listed in the docs at least – scsimon Jun 25 '18 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.