Returning most recent data for multiple entries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning most recent data for multiple entries

Author

Topic

Quailman808
Starting Member

2 Posts

Posted - 2010-02-12 : 17:30:42

Hello all! This is my first posting, but I'm hoping to gain some knowledge in the process.
I'd like to execute an SQL Query that reaches out to multiple tables, joins them on their commonalities, and return only one (1) record for each user. Our DB is set up to record when a user logs in to a system, but the problem is that it holds on to a few of their last log ins for security reasons.
I'd like to return just the most recent log-in date. The code I have included below is what I am currently working with, which returns each log-in session:

SELECT id, lname, fname, people_stats.login_date
FROM mrt_user
join mrt_employee on mrt_user.id = mrt_employee.uid
join mrt_role on mrt_user.id = mrt_role.uid
join mrt_people_stats on mrt_user.id = mrt_people_stats.uid
ORDER BY lname asc;

Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 10:00:52

may be this


SELECT id, lname, fname, people_stats.login_date
FROM mrt_user
join mrt_employee on mrt_user.id = mrt_employee.uid
join mrt_role on mrt_user.id = mrt_role.uid
join mrt_people_stats on mrt_user.id = mrt_people_stats.uid
join (select uid,max(login_date) as recent
      from mrt_people_stats 
      group by uid) tmp
on tmp.uid= mrt_people_stats.uid
and tmp.recent = mrt_people_stats.login_date
ORDER BY lname asc;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Quailman808
Starting Member

2 Posts

Posted - 2010-02-15 : 11:04:44

Visakh16,

Thank you very much for the guidance! With very few changes I have been able to successfully return the requested information :)

Hopefully I will be able to use this as a base to add additional pieces of information if they become necessary.

Thank you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 11:11:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources