How to parse through timestamps and states? (Novice)

Question

I'm not sure how to figure this one out, but here's a 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 to pull just the latest timestamp, so I would just want:

╔════════════╦════════╦═══════════╗
║ Company_ID ║ Status ║ Timestamp ║
╠════════════╬════════╬═══════════╣
║       1234 ║ Test   ║ 4/1/15    ║
║       4567 ║ Test   ║ 2/12/15   ║
║       3456 ║ Test   ║ 4/15/15   ║
╚════════════╩════════╩═══════════╝

Thus far, I want to try this, but I'm afraid it'd just pull the largest timestamp, but not with the associated status. Is this right?

select
    company_id,
    status,
    max(timestamp)
from
    sample
group by 1, 2

EDIT: It's on Redshift (PostgreSQL).

What database is this for? Offhand your query won't work because you'll be getting the same results since the statuses are different. Also your question is pretty similar to this one: http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql — SLin, Apr 27 '15 at 20:23
Sorry about that. Redshift on PostgreSQL. Certain window/partition functions don't work in Redshift for some reason, but the answers below were helpful! — JustCasual, Apr 27 '15 at 22:55
@JustCasual if the answers below were helpful to you, you may vote them up :-) — PhillipD, Apr 28 '15 at 07:49

score 0 · Answer 1 · answered Apr 27 '15 at 20:26

This should work in any modern RDBMS .. I tested in in Oracle .. but should work in SQL*Server, etc.

  with w_data as (
        select 1234 company_id, 'Test' status, to_date('01-apr-2015','dd-mon-yyyy') ctime from dual union all
        select 1234 company_id, 'Live' status, to_date('30-mar-2015','dd-mon-yyyy') ctime from dual union all
        select 3456 company_id, 'Live' status, to_date('30-jan-2015','dd-mon-yyyy') ctime from dual union all
        select 4567 company_id, 'Test' status, to_date('12-feb-2015','dd-mon-yyyy') ctime from dual union all
        select 3456 company_id, 'Test' status, to_date('15-apr-2015','dd-mon-yyyy') ctime from dual
        ),
     w_sub as (
        select company_id, status, ctime,
                 row_number() over (partition by company_id order by ctime desc)  rnum
          from w_data
        )
  Select company_id, status, ctime
    from w_sub
   where 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.

score 0 · Answer 2 · answered Apr 27 '15 at 20:27

0

Try this:

select
 s1.company_id,
 s1.status,
 s1.timestamp
from
 sample s1
JOIN (
 SELECT company_id, max(timestamp) as timestamp
 FROM sample
 GROUP BY company_id) s2
ON s1.company_id = s2.company_id
  AND s1.timestamp= s2.timestamp

answered Apr 27 '15 at 20:27

JustAPup

1,720
12
19

PhillipD · Answer 3 · 2015-04-27T21:43:15.267

If your RDMS supports analytic functions (e.g. Oracle, Postgres, Vertica, ...) you can use something like:

select company_id, status, timestamp from (
    select
    company_id,
    status,
    timestamp,
    row_number() over (partition by company_id order by timestamp desc) as 'n'
    from
    sample
) a where n = 1

The analytic function row_number() numbers the rows company wise with respect to the timestamp (in descending order in this case), i.e. the 'newest' timestamp gets a 1. We then use an outer query and fetch only rows where the row_number is 1.

If you use mySQL you could use the group_concat() function in combination with substring_index()

select company_id, 
substring_index(group_concat(status order by timestamp desc separator ','), ',', 1), 
substring_index(group_concat(timestamp order by timestamp desc separator ','), ',', 1) 
from sample 
group by company_id

(Make sure that your RDMS handles your date format correctly)

score 0 · Answer 4 · answered May 02 '15 at 22:08

0

A slightly simpler query using Window function with first/last_value:

SELECT 
company_id, 
last_value(status) OVER (partition by company_id ORDER BY timestamp),
last_value(timestamp) OVER (partition by company_id ORDER BY timestamp)
FROM sample

answered May 02 '15 at 22:08

Guy

12,388
3
45
67

How to parse through timestamps and states? (Novice)

4 Answers4