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

score 3 · Accepted Answer · answered Dec 19 '16 at 07:46

3

select  *

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

        from        mytable t
        )

where   rn = 1

answered Dec 19 '16 at 07:46

David דודו Markovitz

42,900
6
64
88

score 1 · Answer 2 · answered Dec 19 '16 at 07:49

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

Filtering rows to get only the latest value

2 Answers2