Oracle SQL query: Retrieve latest values per group based on time [duplicate]

Given this data ...

SQL> select * from qtys  2  /        ID TS                      QTY---------- ---------------- ----------         1 2010-01-04 11:00        152         2 2010-01-04 11:00        210         1 2010-01-04 10:45        132         2 2010-01-04 10:45        318         4 2010-01-04 10:45        122         1 2010-01-04 10:30          1         3 2010-01-04 10:30        214         2 2010-01-04 10:30       5515         4 2010-01-04 10:30        2109 rows selected.SQL>

... the following query gives what you want ...

SQL> select x.id  2         , x.ts as "DATE"  3         , x.qty as "QUANTITY"  4  from (  5      select id  6             , ts  7             , rank () over (partition by id order by ts desc) as rnk  8             , qty  9      from qtys ) x 10  where x.rnk = 1 11  /        ID DATE               QUANTITY---------- ---------------- ----------         1 2010-01-04 11:00        152         2 2010-01-04 11:00        210         3 2010-01-04 10:30        214         4 2010-01-04 10:45        122SQL>

With regards to your additional requirements, you can apply additional filters to the outer WHERE clause. Similarly you can join additional tables to the inline view like it was any other table.

Here's a complete, tested example.

CREATE TABLE tbl1 (ID NUMBER, dt DATE, quantity NUMBER);DELETE FROM tbl1;insert into tbl1 values (1,to_date('2010-01-04 11:00','YYYY-MM-DD HH24:MI'), 152);insert into tbl1 values (2,to_date('2010-01-04 11:00','YYYY-MM-DD HH24:MI'), 210);insert into tbl1 values (1,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 132);insert into tbl1 values (2,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 318);insert into tbl1 values (4,to_date('2010-01-04 10:45','YYYY-MM-DD HH24:MI'), 122);insert into tbl1 values (1,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 1);insert into tbl1 values (3,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 214);insert into tbl1 values (2,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 5515);insert into tbl1 values (4,to_date('2010-01-04 10:30','YYYY-MM-DD HH24:MI'), 210);SELECT t.ID     , t.DT     , t.QUANTITY  FROM tbl1 t     ,( SELECT ID             , MAX(dt) dt          FROM tbl1       GROUP BY ID ) t2  WHERE t.id = t2.id    AND t.dt = t2.dt

Results:

1   1/4/2010 11:00:00 AM    1522   1/4/2010 11:00:00 AM    2103   1/4/2010 10:30:00 AM    2144   1/4/2010 10:45:00 AM    122

If you want to get the records for the last XX minutes, you can do this (I'm using 500 minutes in this example, replace the 500 with whatever you desire):

   SELECT t.ID        , t.DT        , t.QUANTITY     FROM tbl1 t        ,( SELECT ID                , MAX(dt) dt             FROM tbl1            WHERE dt >= SYSDATE - (500 / 1400)          GROUP BY ID ) t2     WHERE t.id = t2.id       AND t.dt = t2.dt;