[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • 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 BurtonCommented:
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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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