Q:

SQL query to get the latest price from the data?

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.

A:

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;

