Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL query to get the last selected value per person Expand / Collapse
Author
Message
Posted Wednesday, August 6, 2008 2:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 5, 2009 5:36 AM
Points: 7, Visits: 7
Hi,

I need some help with a SQL query. I've been trying to find a way to get the desired information but so far nothing seems to work right. I have a table which looks like this:
personId int, selectedValue int, logDate datetime

For each person there can be multiple records in the table. What I'd like to do is get the last selected value for each person in the table. I am able to get the last date a person selected something with this query:
SELECT personId, MAX(logDate) FROM MySelectedValues GROUP BY personId 

Unfortunately I've been unable to come up with a query that will also retrieve the selected value. Any help would be much appreciated!
Post #547318
Posted Wednesday, August 6, 2008 2:30 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 4,655, Visits: 11,126
Try with this:

SELECT A.personId, A.LogDate, A.selectedValue
FROM MySelectedValues AS A
INNER JOIN (
SELECT personId, MAX(logDate) AS LogDate
FROM MySelectedValues
GROUP BY personId
) AS B
ON A.personId = B.personId
AND A.LogDate = B.LogDate

Regards
Gianluca


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #547320
Posted Wednesday, August 6, 2008 2:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 5, 2009 5:36 AM
Points: 7, Visits: 7
Thanks, that works really well. You've been a great help!
Post #547332
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse