Get desc or last inserted data while using GROUP BY

Question

Have warm wishes.

I have a table with lots of data and I need all data but the data that only updated last for example

Table

id  -   name    -   user_id -   email
1   -   abc     -   1   -   [email protected]
2   -   xyz     -   2   -   [email protected]
3   -   abc     -   1   -   [email protected]
4   -   pqr     -   3   -   [email protected]
5   -   abc     -   1   -   [email protected]
6   -   pqr     -   4   -   [email protected]

And now i queryu something like this

SELECT MAX(id),* 
FROM `table`
GROUP BY user_id 
ORDER BY id DESC

But it not returns expected output.my expected output is

id  -   name    -   user_id -   email
5   -   abc     -   1   -   [email protected]
2   -   xyz     -   2   -   [email protected]
4   -   pqr     -   3   -   [email protected]
6   -   pqr     -   4   -   [email protected]

The data inserted last only that will be shown. I refer this answer but it does not for getting all data. So how can I get all data that is inserted last when grouping id?

score 2 · Accepted Answer · answered Jun 24 '18 at 09:02

2

You can use self join on same table to pick latest row per user

SELECT a.* 
FROM `table` a
JOIN (
    SELECT user_id , MAX(id) id
    FROM table
    GROUP BY user_id 
) b ON(a.user_id = b.user_id AND a.id = b.id)
ORDER BY a.id DESC

Demo

answered Jun 24 '18 at 09:02

M Khalid Junaid

63,861
10
90
118

score 1 · Answer 2 · answered Jun 24 '18 at 12:07

Especially if you are ordering the final results, you should consider a correlated subquery instead:

select t.*
from t
where t.id = (select max(t2.id)
              from table t2
              where t2.user_id = t.user_id
             )
order by t.id desc;

For performance, you want indexes on (user_id, id) and (id).

Get desc or last inserted data while using GROUP BY

2 Answers2