SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Most recent data by date and ID		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dotsofcolor
Starting Member

12 Posts
Posted - 01/16/2013 :  19:48:55  Show Profile  Reply with Quote
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.

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 01/16/2013 :  21:13:10  Show Profile  Reply with Quote 
Select * from
(Select *,ROW_NUMBER() OVER (PARTITION BY ID Order by Year desc,Month desc) as Seq
from Table
)P
Where P.Seq = 1
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000