[FIXED] DELETE From table Group By

Issue

SQL query:

`SELECT * FROM `adl_auth_user` WHERE `user_id` = 1919`

Result is

id  user_id     type_id     type       auth_    role_id     
66428   1919     1                97    103     6

66429   1919    1                 97    104     6

66430   1919    1                 97    105     6

66431   1919    1                 97    106     6

When I Add Group By

SELECT * FROM `adl_auth_user` WHERE `user_id` = 1919 group by type,type_id

Result is

id  user_id     type_id     type    auth_item_id    role_id     

66428   1919    1   97                103             6

I dont need all Details with query

`SELECT * FROM `adl_auth_user` WHERE `user_id` = 1919` (its simple)

How can i delete table details of following

SELECT * FROM `adl_auth_user` WHERE `user_id` = 1919 group by type,type_id

Note : All details are edited format ...

Solution

This query:

DELETE adl_auth_user
FROM adl_auth_user
LEFT JOIN adl_auth_user t2 
   ON adl_auth_user.type_id = t2.type_id AND
      adl_auth_user.type = t2.type AND
      adl_auth_user.id > t2.id
WHERE adl_auth_user.user_id = 1919 AND t2.id IS NOT NULL

deletes all records having the same type_id and type values except from the one having the smallest id value.

Demo here

Answered By - Giorgos Betsos