Group By

[georgerobbo]

I'm currently trying to get a list of all the students and when they last logged in. When a user is authenticated, a session is created and stored in the Sessions table. The primary key is a SHA1 hash of the time they logged in, their internet protocol address and username.

 

In my query, I only want to get the latest Session in the join. I can't seem to achieve this with GROUP BY. How do I go about doing this? At the moment I'm getting duplicate results.

 

SELECT Users.UserID, Users.Firstname, Users.Surname, Sessions.SessionID, Sessions.IPv4, Sessions.LoginStamp, Sessions.LastActivity FROM Users JOIN Students ON Students.UserID = Users.UserID LEFT JOIN SessionsUsers ON SessionsUsers.UserID = Users.UserID LEFT JOIN Sessions ON Sessions.SessionID = SessionsUsers.SessionID ORDER BY Sessions.LastActivity DESC, Users.Surname ASC

 

Users

-----------

UserID

Firstname

Surname

Hash

 

Students

-----------

UserID

House

LeavingYear

SessionsUsers

------------------

UserID

SessionID

 

Sessions

-----------

SessionID

Ipv4

LoginStamp

ExpireStamp

LastActivity

 

 

I have highlighted the duplicate. How do I group by the last Session. (Effectively ordering by the latest LoginStamp).

 

Thanks

 

 

[attachment deleted by admin]

[joel24]

if you only want the latest session for each user, group by USER_ID and ORDER BY LastActivity