CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Smith
Smith

Reputation: 5951

select top records from multiple entries

I have a table which list loggin users each time they login. the table is like this

id  |  user | cmd   | ts
1   |  john | login | 2014-01-19 18:26:01
2   |  mark | login | 2014-01-19 18:36:00
3   |  john | login | 2014-01-19 18:40:13
4   |  dave | login | 2014-01-19 18:41:58
5   |  john | login | 2014-01-19 18:50:59
6   |  mark | login | 2014-01-19 19:03:23
7   |  dave | login | 2014-01-19 19:15:13
8   |  mark | login | 2014-01-19 19:26:58

i need to select only the latest login from this table, so the output i expect is

id  |  user | cmd   | ts
5   |  john | login | 2014-01-19 18:50:59
7   |  dave | login | 2014-01-19 19:26:58
8   |  mark | login | 2014-01-19 19:15:13

this is what i tried

SELECT * FROM `logins` group by user having count(*) <= 1 order by ts

i got zero rows

Upvotes: 0

Views: 76

Answers (3)

murtaza.webdev
murtaza.webdev

Reputation: 3641

SELECT user,ts,count(*) FROM `logins`  LIMIT 3  group by user order by ts DESC

Upvotes: 0

Mihai
Mihai

Reputation: 26784

SELECT id,user,cmd,MAX(ts) ts FROM `logins`
group by user

Upvotes: 2

Cjmarkham
Cjmarkham

Reputation: 9681

Your order is missing a direction. Try adding either ASC or DESC. In your case it would be DESC

order by ts DESC

Upvotes: 1

Related Questions