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
 Most recent data by date and ID

Author  Topic 

dotsofcolor
Starting Member

12 Posts

Posted - 2013-01-16 : 19:48:55
Hello,

I need some help getting specific records from a data set.

The data connection is coming from an AS/400 system using SPSS 9.0 for the program that I use for queries.

I tried using the MAX function in SQL but gather that I am not using it correctly or should be using something else all together.

I did a mock up of some data for an example, sorry about the formatting.

Year Month ID Amount CorrType
2012 10 123456 50 S
2012 11 123456 22.5 S1
2012 12 123456 75 L3
2013 1 123456 100 L4
2011 5 987654 2 S
2012 9 987654 8 S1
2012 10 987654 22 L3
2012 11 987654 16 S
2012 9 554422 10 S
2012 10 554422 20 S1
2012 11 554422 30 L3
2012 12 554422 40 L4
2013 1 554422 50 L5
2012 5 999999 10 S
2012 7 999999 -2 S
2012 10 999999 30 S
2013 1 999999 70 S1

I need to display the most recent date by ID field from the above records so it shows like the following.

Year Month ID Amount CorrType
2013 1 123456 100 L4
2012 11 987654 16 S
2013 1 554422 50 L5
2013 1 999999 70 S1

Let me know if you need further detail. Appreciate any help.[url][/url][url][/url]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-16 : 21:13:10
[code]Select * from
(Select *,ROW_NUMBER() OVER (PARTITION BY ID Order by Year desc,Month desc) as Seq
from Table
)P
Where P.Seq = 1[/code]
Go to Top of Page
   

- Advertisement -