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!

Possible duplicate of: https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql — Lawynn Jana, Feb 06 '21 at 06:17
@Henry Please check code and let me does it serve your purpose or not. :-) — Srijon Chakraborty, Feb 06 '21 at 06:47
Please define "last" - rows in a relational database don't have any implied sort order. So unless you have a column that can be used to order the rows, there is no way to extract the "last" row per user. — , Feb 06 '21 at 08:49
a_horse_with_no_name is right. What you are showing in your example is not the last row per user, but the maximum product. The last rows can not be determined, because there is nothing in the table to indicate that order. Getting the maximum product per user is simply: `select max(product), user_id from mytable group by user_id`. — Thorsten Kettner, Feb 06 '21 at 08:58

score 3 · Accepted Answer · answered Feb 06 '21 at 06:44

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 ;

score 2 · Answer 2 · answered Feb 06 '21 at 12:56

There is no such thing as a "last" record unless you have a column that specifies the ordering. SQL tables represent unordered sets (well technically, multisets).

If you have such a column, then use distinct on:

select distinct on (user_id) t.*
from t
order by user_id, <ordering col> desc;

Distinct on is a very handy Postgres extension that returns one row per "group". It is the first row based on the ordering specified in the order by clause.

score 1 · Answer 3 · answered Feb 06 '21 at 06:13

1

You can try using row_number()

select product,iserid
from
(
select product, userid,row_number() over(partition by userid order by product desc) as rn
from tablename
)A where rn=1

answered Feb 06 '21 at 06:13

Fahmi

37,315
5
22
31

score 0 · Answer 4 · answered Feb 06 '21 at 06:35

You should have a column that stores the insertion order. Whether through auto increment or a value with date and time.

Ex:

autoIncrement	produt	user_id
1	A	15
2	B	15
3	A	16
4	C	16

SELECT produt, user_id FROM table inner join 
     ( SELECT MAX(autoIncrement) as id FROM table group by user_id ) as table_Aux
     ON table.autoIncrement = table_Aux.id

How to select the last record of each ID

4 Answers4