Selecting a unique maximum value for each person

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

Selecting a unique maximum value for each person

Author

Topic

forthemame
Starting Member

4 Posts

Posted - 2009-09-22 : 19:45:26

Probably not the best of subject lines, but hopefully the following description will clear things up a bit.

I have two tables: People and Class Info. The People table stores names and SSN's, while the Class Info table stores information about classes they've completed and the year in which they completed them in.

Person
SSN Name
111 Adam
222 Bill
333 Carol

Class Info
SSN ClassID Year
111 1 2005
111 2 2006
111 3 2006
222 1 2008
222 3 2008
333 1 2007
333 4 2008

How do I write a query to get the only the class info for the most recent year a person took a class? I tried the following, but it seems to only grab the maximum year overall instead of the maximum year per person.

Select P.Name, C.ClassID, C.Year
From Person P, Class_Info C
Where C.ClassID = (Select MAX(C1.ClassID) From Class_Info C1 Where P.SSN = C1.SSN)

The above query returns
Bill, 1, 2008
Bill, 2, 2008
Carol, 4, 2008

But I would like it to return
Adam, 2, 2006
Adam, 3, 2006
Bill, 1, 2008
Bill, 2, 2008
Carol, 4, 2008

Thoughts? Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-22 : 20:07:54

SELECT t.SSN, t.ClassID, C.Year
FROM Class_Info C
JOIN
(
Select P.SSN, MAX(C.ClassID) AS ClassID
From Person P
JOIN Class_Info C
ON P.SSN = C.SSN
GROUP BY P.Name) t
ON C.SSN = t.SSN

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 02:51:04

Also refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

forthemame
Starting Member

4 Posts

Posted - 2009-09-23 : 18:21:27

quote:
Originally posted by tkizer

SELECT t.SSN, t.ClassID, C.Year
FROM Class_Info C
JOIN
(
Select P.SSN, MAX(C.ClassID) AS ClassID
From Person P
JOIN Class_Info C
ON P.SSN = C.SSN
GROUP BY P.Name) t
ON C.SSN = t.SSN

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Doesn't help me but thanks anyway.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-24 : 00:15:39

Umm okay. I suppose it would have been better if you explained why it didn't help that way we can try again.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

forthemame
Starting Member

4 Posts

Posted - 2009-09-24 : 14:20:48

quote:
Originally posted by tkizer

Umm okay. I suppose it would have been better if you explained why it didn't help that way we can try again.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Well I'm working in Peoplesoft which uses a different syntax of SQL it seems. Also this is just a small portion of my query, as the main one is much closer to ~100 lines. That makes it hard to plug in this small piece in the large puzzle.

Finally I'm not entirely sure I see your query working correctly actually.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-24 : 16:41:45

It should work, I use that approach all of the time. If Peoplesoft can't handle that type of syntax, then perhaps you can put it into a stored procedure to hide it from Peoplesoft.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

forthemame
Starting Member

4 Posts

Posted - 2009-09-24 : 17:16:39

quote:
Originally posted by tkizer

It should work, I use that approach all of the time. If Peoplesoft can't handle that type of syntax, then perhaps you can put it into a stored procedure to hide it from Peoplesoft.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Ok maybe you're right.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources