2

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

Gayan
  • 2,845
  • 7
  • 33
  • 60
  • A GROUP BY clause with no aggregate function (eg: COUNT(), SUM(), MAX()) makes no sense at all. It baffles me that MySQL even allows this. Think about it, why are you grouping if you don't do anything with the groups? [said here](https://stackoverflow.com/questions/10030787/using-order-by-and-group-by-together) – danish-khan-I May 21 '19 at 04:37
  • Your second query is almost right, you should just remove the last line `GROUP BY user_data.user_id ORDER BY user_data.id DESC` – Nick May 21 '19 at 04:48

1 Answers1

1

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
Fahmi
  • 37,315
  • 5
  • 22
  • 31