CODEWITHSUNDEEP
mysql
M. of CA
M. of CA

Reputation: 1546

MySQL GROUP BY return the first item - Need to select the last item

I have a table that has duplicate data for the same user_id. I need to select the newest record for each user_id. When i use group by then order. mysql does the function in that order and i get the wrong records.

Table - tblUsersProfile

Field Type Null Default Comments id int(11) No AI
user_id int(7) No
first_name_id int(11) No
last_name_id int(11) No
location_id int(11) No
dob date Yes NULL
sex int(1) Yes NULL 1 for Male, 0 for Female created_by int(21) No
activity_ts timestamp No CURRENT_TIMESTAMP

Upvotes: 0

Views: 235

Answers (1)

Nicola Cossu
Nicola Cossu

Reputation: 56357

select t1.* from tblUsersProfile as t1
inner join (
select user_id,max(activity_ts) as rct
from tblUsersProfile
group by user_id) as t2
on t1.user_id = t2.user_id and t1.activity_ts = t2.rct

Maybe my query is even more "complicated" than necessary if you have all the others data redundants and equals for all records.

Upvotes: 2

Related Questions