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

Question

I have the following table in an Oracle DB

id     date              quantity 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  210 

now I'd like to retrieve the latest value (and its time) per id. Example output:

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  122 

I just can't figure out how to put that into a query...

Additionally the following options would be nice:

Option 1: the query should only return values that are from the last XX minutes.

Option 2: the id should be concatenated with text from another table that has id and idname. output for id should then be like: id-idname (eg 1-testid1).

many thanks for any help!

Answer 1

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        210  9 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        122  SQL> 

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.

Answer 2

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    152 2   1/4/2010 11:00:00 AM    210 3   1/4/2010 10:30:00 AM    214 4   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;