Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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 CorrType2012 10 123456 50 S2012 11 123456 22.5 S12012 12 123456 75 L32013 1 123456 100 L42011 5 987654 2 S2012 9 987654 8 S12012 10 987654 22 L32012 11 987654 16 S2012 9 554422 10 S2012 10 554422 20 S12012 11 554422 30 L32012 12 554422 40 L42013 1 554422 50 L52012 5 999999 10 S2012 7 999999 -2 S2012 10 999999 30 S2013 1 999999 70 S1I need to display the most recent date by ID field from the above records so it shows like the following.Year Month ID Amount CorrType2013 1 123456 100 L42012 11 987654 16 S2013 1 554422 50 L52013 1 999999 70 S1Let 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 Seqfrom Table)PWhere P.Seq = 1[/code]