CODEWITHSUNDEEP
mysql
Gayan
Gayan

Reputation: 2935

MySQL order by before group by did not work

I have two tables

users

user_id | name
1       | John
2       | Jess

user_data (in_out - 1 indicate in, 0 indicate out)

id | user_id | in_time              | out_time             | in_out
1  | 1       | 2019-05-14 09:15:32  | 2019-05-14 10:45:32  | 0
2  | 1       | 2019-05-15 10:15:32  | 0000-00-00 00:00:00  | 1
3  | 2       | 2019-05-16 11:15:32  | 2019-05-16 12:15:32  | 0

I want to get latest entries of each user, but group by and order by did not work well.

First I tried following way, but this is not give latest records (I want to get record id 2 and 3 from user_data table, but it returns record id 1 and 3)

SELECT *, user.user_id as user_key 
FROM user 
LEFT JOIN user_data ON user_data.user_id = user.user_id 
GROUP BY user_data.user_id ORDER BY user_data.id DESC

Secondly I tried following way, I wrote this query following an answer of stackoverflow, but it did not work. 

SELECT *, user.user_id as user_key 
FROM user 
LEFT JOIN 
( 
SELECT MAX(user_data.id) as max_record_id, user_data.user_id 
FROM user_data 
GROUP BY user_data.user_id 
) u2 ON u2.user_id = user.user_id 
GROUP BY user_data.user_id ORDER BY user_data.id DESC

Someone please help me to solve this issue. Thank You

Upvotes: 2

Views: 59

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try using a correlated subquery

SELECT *, user.user_id as user_key 
FROM user 
LEFT JOIN user_data u2 ON u2.user_id = user.user_id 
where u2.id in (select MAX(u3.id) from user_data u3 where u2.user_id=u3.user_id)
ORDER BY u2.id

Upvotes: 1

Related Questions