How to select the last record of each ID

Question

I need to extract the last records of each user from the table. The table schema is like below.

mytable

product | user_id |
-------------------
   A    |   15    |
   B    |   15    |
   A    |   16    |
   C    |   16    |
-------------------

The output I want to get is

product | user_id |
-------------------
   B    |   15    |
   C    |   16    |

Basically the last records of each user.

Thanks in advance!

Answer 1

You can use a window function called ROW_NUMBER.Here is a solution for you given below. I have also made a demo query in db-fiddle for you. Please check link Demo Code in DB-Fiddle

WITH CTE AS
(SELECT product, user_id,
       ROW_NUMBER() OVER(PARTITION BY user_id order by product desc)
       as RN
FROM Mytable)
SELECT product, user_id FROM CTE WHERE RN=1 ;