26

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!

Improve this question
2
  • Is DATE unique for a given ID? Commented Jan 4, 2010 at 17:32
  • the timestamp should be unique, but apparently it is not in the data I have available -- so the solution should work regardless. Commented Jan 4, 2010 at 19:19

2 Answers 2

Reset to default
34

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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.
would it be better performance-wise to add the DISTINCT to the inner or the outer select?
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.
Tom - that sounds like something you ought to ask as a new question.
@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.
|
11

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;
Improve this answer

1 Comment

I voted for your answer as well, I just prefer the syntax of the other statement.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.