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.

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;