Filtering rows to get only the latest value

Question

I have the following data in a table:

ORDERID, PRODUCT,  QUANTITY
1,       potatoes, 10
1,       oranges,  20
2,       apples,   10
2,       oranges,  15
3,       pears,    20
3,       peaches,  12

I would like to query the table to filter out repeated products (e.g. oranges), taking only the latest (higher ORDERID) value. This should then result in:

ORDERID, PRODUCT,  QUANTITY
1,       potatoes, 10
2,       apples,   10
2,       oranges,  15
3,       pears,    20
3,       peaches,  12

Answer 1

select  *

from   (select      t.*
                   ,row_number () over (partition by PRODUCT order by ORDERID desc) as rn

        from        mytable t
        )

where   rn = 1

Answer 2

An alternative to the answer given by @Dudu would be to subquery to find the maximum order ID for each product, and then join this back to your original table to restrict to the records you want to see.

SELECT t1.ORDERID,
       t1.PRODUCT,
       t1.QUANTITY
FROM yourTable t1
INNER JOIN
(
    SELECT PRODUCT, MAX(ORDERID) AS MAX_ORDERID
    FROM yourTable
    GROUP BY PRODUCT
) t2
    ON t1.PRODUCT = t2.PRODUCT AND
       t1.ORDERID = t2.MAX_ORDERID