Tuan Anh Tuan Anh - 1 month ago 10
SQL Question

Fetch first row only with IN operation in DB2

how do i use "fetch first row only" in combination with "in" operator? something like

select user_id, item_name as last_item_name
from sale
where user_id in (1,2)
order by date desc
fetch first row only


which i want it to returns the last item each person buy?
eg: running the query on

--------------------------------
id | user_id | item_name| date |
1 | 1 | pen | 01/01|
2 | 1 | book | 02/01|
3 | 2 | book | 01/01|
4 | 2 | computer | 02/01|
--------------------------------


should return

user_id | last_item_name|
1 | book |
2 | computer |
-------------------------


Thanks!

Answer

So you want latest per user? Use ROW_NUMBER() :

SELECT * FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY t.user_id ORDER BY t.date DESC) as rnk
    FROM sale t
    WHERE t.user_id IN(1,2)) s
WHERE s.rnk = 1