Help finding 'MAX' - SQL Server Forums

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

Help finding 'MAX'

Author

Topic

lbeese
Starting Member

24 Posts

Posted - 2011-02-23 : 14:05:48

Hi,
I have a member_coverage table with the following fields and data:
Member_id benefit_product enrollment_date
123 ABC 1/1/2011
123 ABC 1/1/2010
123 ABC 1/1/2009
123 ABC 1/1/2008
123 DEF 1/1/2007

I need to pull all 3 fields but want only the 1/1/2011. Of course when I use MAX(enrollment_date) I get both the 1/1/2011 and the 1/1/2007 enrollments because of their different benefit_products. How do I get only the 1/1/2011 enrollment?
Any assistance is appreciated.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-02-23 : 14:28:27

Maybe this?

SELECT TOP 1 *
FROM YourTable
ORDER BY enrollment_date DESC

Some days you're the dog, and some days you're the fire hydrant.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-23 : 14:29:19

SELECT *
FROM

(select member_id,benefit_product,enrollment_date
,[maxDate] = rank() over(partition by member_id order by enrollment_date desc)
from yourTable
) t

WHERE maxdate = 1

Jim

Everyday I learn something that somebody else already knew

lbeese
Starting Member

24 Posts

Posted - 2011-02-23 : 15:27:32

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources