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

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!

the timestamp should be unique, but apparently it is not in the data I have available -- so the solution should work regardless. — Tom, Jan 04 '10 at 19:19

score 33 · Accepted Answer · answered Jan 04 '10 at 17:54

33

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.

answered Jan 04 '10 at 17:54

APC

144,005
19
170
281

thanks, the basic query is working fine. just found out that timestamp is not always unique, so I got multiple entries per id, but adding a DISTINCT to the beginning helped. will try the additional options now. – Tom Jan 04 '10 at 19:23
would it be better performance-wise to add the DISTINCT to the inner or the outer select? – Tom Jan 04 '10 at 19:25
DISTINCT won't change the results you get, unless by chance the `quantity` values match for the maximum `date` values. You need to discover what the appropriate business rule is, and apply it. Possibilities include `max(quantity)`, `min(quantity)` or `avg(quantity)`, but there are many possible resolutions. – APC Jan 05 '10 at 00:30
they are duplicates in both timestamp and quantity, so distinct works. on a related note, is there a more efficient method of retrieving only the single most recent record from an oracle db than the following query: . select * from (select quantity from table order by time desc) where ROWNUM <= 1 . – Tom Jan 05 '10 at 10:36
1

Tom - that sounds like something you ought to ask as a new question. – APC Jan 05 '10 at 11:06
nevermind, it works just fine, just thought of it as it is a related query.. not really worth it's own entry here I think ;) – Tom Jan 05 '10 at 11:27
1

@Tom, if you always need a single record per `ID`, you could use `ROW_NUMBER()` analytic function instead of `RANK`, as `RANK` indeed could be `1` for several records with same values of sorting fields. But this wouldn't be deterministic. of course. – Olexa Sep 21 '22 at 12:17

dcp · Answer 2 · 2010-01-04T17:59:50.140

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;

I voted for your answer as well, I just prefer the syntax of the other statement. — Tom, Jan 04 '10 at 19:43

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

2 Answers2

Linked

Related