Simple MYSQL Group By and Join

Question

Table users

id - Primary Key, AI
username - varchar (50)

Table logins

login_time - DateTime
member_id - int (11)

This SQL selects mid and max login_time and sorts DESC
SELECT member_id, max(login_time) as time FROM logins GROUP BY member_id ORDER BY time DESC

Question
How do you select and join username onto member_id for each row?

dockeryZ · Accepted Answer · 2010-07-25 05:57:26Z

1

SELECT u.username, max(l.login_time) as time FROM logins l
LEFT JOIN users u ON (l.member_id = u.id)
GROUP BY l.member_id ORDER BY time DESC

answered Jul 25, 2010 at 5:57

dockeryZ

4,0011 gold badge22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Imre L · Accepted Answer · 2010-07-25 05:54:06Z

1

SELECT l.member_id, max(l.login_time) as time, u.username FROM logins as l
join users as u on u.id = l.member_id
GROUP BY l.member_id ORDER BY l.time DESC

answered Jul 25, 2010 at 5:54

Imre L

6,29126 silver badges32 bronze badges

1 Comment

rrrfusco Over a year ago

MYSQL throws an error for ORDER BY l.time. I prefer to write 'table t', rather than use the keyword 'as' which is generally used for column aliases.

Collectives™ on Stack Overflow

Simple MYSQL Group By and Join

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related