• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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'
0
Ess Kay
Asked:
Ess Kay
1 Solution
 
Phillip BurtonDirector, 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now