CODEWITHSUNDEEP
mysqlsql
Saeid
Saeid

Reputation: 448

Making a mysql query with group by and order by

I have a MySQL table like this:

login date is timestamp

id       userID          loginDate
1        524             1546538626
2        524             1546538649
3        1               1546539656
4        925             1546539681
5        1073            1546544363
6        1               1546544731
7        524             1546548094
8        1073            1546548169
9        1073            15465922

I want to write a query that shows which users has last login and also have the logindate (without duplications). I have tried this query but it shows each users first login !

SELECT * from 
   (select * from lastlogin order by id desc)t 
  GROUP by userID

I dont know how can i write this . can anyone help? Thankyou

Upvotes: 1

Views: 32

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

If you wanted to get additional information from the login (such as the id), you can use a correlated subquery:

select ll.*
from lastlogin ll
where ll.id = (select ll2.id
               from lastlogin ll2
               where ll2.userId = l.userId
               order by ll2.date desc
               limit 1
              );

Upvotes: 1

santosh thapa
santosh thapa

Reputation: 11

SELECT f.*
FROM fzxit_lastlogin  f
INNER JOIN
    (SELECT *, MAX(loginDate) AS MaxDateTime
    FROM fzxit_lastlogin
    GROUP BY userID) t 
ON f.id = t.id
AND f.loginDate = t.MaxDateTime

Upvotes: 0

PSK
PSK

Reputation: 17943

You can try like following.

select userId,max(logindate) md
from fzxit_lastlogin 
group by userid
order by userid

Upvotes: 1

Related Questions