Search code examples
sqlgreatest-n-per-group

How can I query SQL for latest record with additional conditions

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?

Solution

  • --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