Query on last timestamp

Toad Data Point
1

Hello,

I’m using TDA to query a Oracle DB, i do have a query but i only want to see the last timestamp and not all timestamps,
is there any way to show only the last ITEM_PRICE.TIMESTAMP ?

SELECT ITEM_PRICE.ITEM_ID, ITEM_PRICE.ITEM_PRICE, ITEM_PRICE.TIMESTAMP
FROM DB120.ITEM_PRICE ITEM_PRICE
WHERE (ITEM_PRICE.ITEM_ID = ‘A-001’)

ITEM_ID * ITEM_PRICE TIMESTAMP
A-001 64,72 2007-08-03 7:58
A-001 89,05 2005-08-24 20:07
A-001 89,05 2005-09-19 12:12
A-001 100,84 2006-03-30 6:07

Thanks and regards,
Dave.

2

You could use max(item_price.timestamp). That would give you rows only for the last timestamp.

SELECT ITEM_PRICE.ITEM_ID, ITEM_PRICE.ITEM_PRICE, MAX(ITEM_PRICE.TIMESTAMP )
FROM DB120.ITEM_PRICE ITEM_PRICE
WHERE (ITEM_PRICE.ITEM_ID = ‘A-001’)
GROUP BY ITEM_PRICE.ITEM_ID, ITEM_PRICE.ITEM_PRICE

Debbie

3

Thanks for your reaction Debbie but unfortunately i get the same result with this query.

Regards,
Dave

4

Can you execute your query in the editor and then save as a *tef file and send to me? (Toad Editor File) This will give the SQL as well as results. With this I can take a look.

Debbie

5

Dave40nl,

You probably want to select the first row of a reversely sorted set, like this:

SELECT TOP 1
ITEM_PRICE.ITEM_ID, ITEM_PRICE.ITEM_PRICE, ITEM_PRICE.TIMESTAMP
FROM DB120.ITEM_PRICE ITEM_PRICE
WHERE (ITEM_PRICE.ITEM_ID = ‘A-001’)
ORDER BY
ITEM_PRICE.TIMESTAMP DESC;

This will sort the results time stamp descending and then select the top 1 row, which is your most recent timestamp.

Use ASC sort to select the oldest date.

Let me know if this isn’t what you wanted.

6

Thanks Debbie. i attched the TEF file.
Query on last timestamp.tef (7.39 KB)

7

Hi Voitek, thanks for your reaction, but this will only sort the row, i’m looking for only one result, the last update for an item, i don’t want to see the rest, Thanks and Regards Dave.

8

Yes, it will display only one result, the one with the most recent date.

9

Hello Dave, in my experience the best way to get help for a query is to show some sample data, and then the expected output.
In your case:
input data is

ITEM_ID * ITEM_PRICE TIMESTAMP
A-001 64,72 2007-08-03 7:58
A-001 89,05 2005-08-24 20:07
A-001 89,05 2005-09-19 12:12
A-001 100,84 2006-03-30 6:07

What is you expected output?

Would either of these queries give you the expected output?
SELECT max (ITEM_PRICE.TIMESTAMP)
FROM DB120.ITEM_PRICE ITEM_PRICE
WHERE (ITEM_PRICE.ITEM_ID = ‘A-001’) ;

or

SELECT ITEM_PRICE.ITEM_ID, max (ITEM_PRICE.TIMESTAMP)
FROM DB120.ITEM_PRICE ITEM_PRICE
WHERE (ITEM_PRICE.ITEM_ID = ‘A-001’)
group by item_price.item_id ;

10

this is another possibility, assuming that timestamp is unique in your system, i.e. there are not two records for the same item_id with the same timestamp.

SELECT ITEM_PRICE.ITEM_ID, ITEM_PRICE.ITEM_PRICE, ITEM_PRICE.TIMESTAMP
FROM DB120.ITEM_PRICE ITEM_PRICE
WHERE (ITEM_PRICE.ITEM_ID = ‘A-001’)
and item_price.timestamp =
(select max (timestamp) FROM DB120.ITEM_PRICE
WHERE ITEM_PRICE.ITEM_ID = ‘A-001’) ;

11

Hi Jacquesrk,

There are no two records for the same item_id and the same timestamp, the last query show me only the last timestamp, i would like to thank you and all other for
your support !

Thanks and regards,
Dave

12

You’re welcome. I’m glad we found the correct query for you.