I have this data in my Oracle table:
In this table, I have multiple products with having product_id
and price at a particular time.
I want to write a SQL query to get latest price of each product based on the TIME
column (Oracle – Long Type).
But here is the problem, for our table price monitoring for the product starts at 23:00 previous date.
Output required: when I passed 2-Dec-2019 as parameter into the SQL query
How can I do this with a SQL query? Or do I need to write a stored procedure for it, based on if and else condition?
Thanks in advance.
Advertisement
Answer
We can try using ROW_NUMBER
for this requirement:
WITH cte AS ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY time DESC) rn FROM yourTable t WHERE system_date = date '2019-12-19' ) SELECT local_date, system_date, currency, product_id, time, current_price FROM cte WHERE rn = 1;