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.

1 Answer 1

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

6
  • the second query might not work if you have multiple rows with the same value in 'created' column Jun 25, 2018 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
    – S3S
    Jun 25, 2018 at 19:40
  • Youre right, might not happen in real world. I have duplicated data in my table that I put for testing. Jun 25, 2018 at 19:43
  • By the way? Any idea if the query would run in mysql and oracle as well? Jun 25, 2018 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
    – S3S
    Jun 25, 2018 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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