i'm not sure how figure 1 out, here's sample table:
╔════════════╦════════╦═══════════╗ ║ company_id ║ status ║ timestamp ║ ╠════════════╬════════╬═══════════╣ ║ 1234 ║ test ║ 4/1/15 ║ ║ 1234 ║ live ║ 3/30/15 ║ ║ 3456 ║ live ║ 1/30/15 ║ ║ 4567 ║ test ║ 2/12/15 ║ ║ 3456 ║ test ║ 4/15/15 ║ ╚════════════╩════════╩═══════════╝ i'd want pull latest timestamp, want:
╔════════════╦════════╦═══════════╗ ║ company_id ║ status ║ timestamp ║ ╠════════════╬════════╬═══════════╣ ║ 1234 ║ test ║ 4/1/15 ║ ║ 4567 ║ test ║ 2/12/15 ║ ║ 3456 ║ test ║ 4/15/15 ║ ╚════════════╩════════╩═══════════╝ thus far, want try this, i'm afraid it'd pull largest timestamp, not associated status. right?
select company_id, status, max(timestamp) sample group 1, 2 edit: it's on redshift (postgresql).
this should work in modern rdbms .. tested in in oracle .. should work in sql*server, etc.
w_data ( select 1234 company_id, 'test' status, to_date('01-apr-2015','dd-mon-yyyy') ctime dual union select 1234 company_id, 'live' status, to_date('30-mar-2015','dd-mon-yyyy') ctime dual union select 3456 company_id, 'live' status, to_date('30-jan-2015','dd-mon-yyyy') ctime dual union select 4567 company_id, 'test' status, to_date('12-feb-2015','dd-mon-yyyy') ctime dual union select 3456 company_id, 'test' status, to_date('15-apr-2015','dd-mon-yyyy') ctime dual ), w_sub ( select company_id, status, ctime, row_number() on (partition company_id order ctime desc) rnum w_data ) select company_id, status, ctime w_sub rnum = 1 / results:
company_id stat ctime ---------- ---- -------------------- 1234 test 01-apr-2015 00:00:00 3456 test 15-apr-2015 00:00:00 4567 test 12-feb-2015 00:00:00 3 rows selected. 