SQL Select distinct, where max day and year

I have a table with these fields (for simplicity)

MemberNum(int),     NoteMonth (int),  NoteYear(int), Notes(varchar(max))
-----------------------------------------------------------------------------------------------------
100000,   1 , 2014, 'ABC'
100000,  2, 2014, 'CDE'
100000, 1, 2015, 'HEL'
200000, 12, 2013, 'AFG'
200000, 5, 2014,  'MMF'
200000, 7, 2015, 'PPP'



Every month a record is added with the year, and month

I need to get the latest Notes for each member


The Query Should Return the following rows

100000, 1, 2015, 'HEL'
200000, 7, 2015, 'PPP'
LVL 15
Ess KayEntrapenuerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
How about this:

With myTable as (
Select MemberNum, NoteMonth, NoteYear, Notes, 
Row_Number() Over(partition by MemberNum Order by NoteYear Desc, NoteMonth Desc) as MyRowNumber
From TableName)
Select MemberNum, NoteMonth, NoteYear, Notes
from myTable
Where MyRowNumber = 1

Open in new window

0
 
Ess KayEntrapenuerAuthor Commented:
Perfect Thanks
0
All Courses

From novice to tech pro — start learning today.