How can I query SQL for latest record with additional conditions

Question

I should find all active vm_id's for a certain date (2014-10-01), important I should reach only latest record per vm_id.

date,action,vm_id,vm_status
2014-09-01 08:13:00,Creation,vm-4058,active
2014-09-04 10:13:00,Creation,vm-4059,active
2014-09-08 17:13:00,Deletion,vm-4059,cancelled
2014-09-30 09:13:00,Creation,vm-4057,active
2014-09-30 15:13:00,Modification,vm-4057,active
2014-10-23 08:13:00,Deletion,vm-4057,cancelled

I would like to fetch the latest record for all active subscription on 2014-10-01:

2014-09-01 08:13:00,Creation,vm-4058,active
2014-09-30 15:13:00,Modification,vm-4057,active

Does anyone know how to get there?

You can go to this post http://stackoverflow.com/questions/25998210/mysql-most-recent-enter-for-each-id/26004212#26004212 that explain more about the same issue. — IgorAlves, Sep 23 '14 at 20:39

score 0 · Accepted Answer · answered Sep 23 '14 at 15:27

0

--Try this

select Max(date) as date, max(action)as action, vm_id, Max(vm_status) from [tablename]

where vm_status = 'active' and vm_id not in (select vm_id from [tablename] where vm_status ='cancelled')

group by vn_id

answered Sep 23 '14 at 15:27

David Kelly

16
1

Also here very close, but it gives me only vm-4058 back and not vm-4059 which was deleted after '2014/10/01' and should be therefore returned. – Philippe Sep 23 '14 at 15:58
OK, here even I was able to add the needed part: select Max(date) as date, max(action)as action, vm_id, Max(vm_status) from t where vm_status = 'active' and vm_id not in (select vm_id from t where vm_status ='cancelled' AND date < '2014/10/01') group by vm_id This it is! Muchos Gracias! – Philippe Sep 23 '14 at 16:00

score 0 · Answer 2 · answered Sep 23 '14 at 15:32

Here is the idea. Find the latest date, then find the record and determine if it is active.

select t.*
from table t join
     (select vm_id, max(date) as maxdate
      from table t
      group by vm_id
     ) tt
     on t.vm_id = tt.vm_id and t.date = tt.maxdate
where t.status = 'active';

score 0 · Answer 3 · edited Sep 23 '14 at 15:44

0

My two cents, get the latest and greatest prior to your date, and make sure it's not a deletion. Same idea as some of the other answers, just another way to do it.

select date,action,vm_id,vm_status from

(SELECT  *,
        ROW_NUMBER() OVER
        (
                PARTITION BY  A.vm_id
                ORDER BY      A.date DESC
        ) AS seq
FROM tablename where date < '2014/10/01') b
where seq = 1 and vm_status <> 'Deletion'

edited Sep 23 '14 at 15:44

answered Sep 23 '14 at 15:40

Peteski

1

This is getting very close, but it does also display the vm-4059 which was cancelled before '2014/10/01' and is therefore not active anymore and should not be displayed. – Philippe Sep 23 '14 at 15:53

How can I query SQL for latest record with additional conditions

3 Answers3