Oracle SQL query: Retrieve latest values per group based on time [duplicate]
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!
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.
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;