3

I have 2 tables : User and LoginLogs and User and LoginLog have 'has Many' relationships. I have to get last login of user from the LoginLog table. I have to query the details of last login user using group by. Here is the Query:

SELECT * FROM `login_logs` 
AS `LoginLog` 
LEFT JOIN `users` 
AS `User` 
ON (`LoginLog`.`user_id` = `User`.`id`) 
WHERE 1 = 1 GROUP BY `LoginLog`.`user_id` 
ORDER BY `LoginLog`.`login_datetime` DESC LIMIT 20

This Query returns first login record.

Archna Rangrej
  • 664
  • 3
  • 14
  • 38

2 Answers2

0

why you are using limit 20 ? Use limit 0,1 For find first record order by login_datetime DESC

try this 

SELECT * FROM `login_logs` 
AS `LoginLog` 
LEFT JOIN `users` 
AS `User` 
ON (`LoginLog`.`user_id` = `User`.`id`) 
WHERE 1 = 1  ORDER BY `LoginLog`.`login_datetime` DESC LIMIT 0 ,1
Bineet Chaubey
  • 527
  • 3
  • 13
-1

In pseudocode : 

 SELECT * FROM LoginLog 
 WHERE MAX(LoginLog.primary_id) AND user.id = loginLog.userId

assuming that there is auto increment PK column in LoginLog

do not forget to index userId columns to get better performance

Martin V.
  • 3,560
  • 6
  • 31
  • 47