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.
Author |
Topic |
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-06-21 : 08:56:08
|
HiI'm trying to determine how to determine the most recent payment date for each loan number in my example. I would like the results to be[url]https://www.dropbox.com/s/f3d9ej5yvuoybbm/mostrecentdate.jpg[/url]Loan Number Payment Dates1 10/20/20101 10/20/20111 10/20/20121 10/20/20132 7/27/20102 7/27/20112 7/27/20122 7/27/2013 Desired Results Loan Number Payment Dates1 10/20/20132 7/27/2013Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-21 : 09:06:45
|
[code]SELECT [Loan Number],[Payment Dates]FROM(SELECT ROW_NUMBER() OVER (PARTITION BY [Loan Number] ORDER BY [Payment Dates] DESC) AS Seq,*FROM Table)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-06-21 : 09:33:03
|
thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-21 : 10:05:51
|
wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-06-21 : 11:20:09
|
visakh16, I like your solution, however, I was wondering why would this not work?SELECT [Loan Number], MAX([Payment Dates]) AS [Payment Dates]FROM Table GROUP BY [Loan Number]; djj |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-21 : 12:39:02
|
quote: Originally posted by djj55 visakh16, I like your solution, however, I was wondering why would this not work?SELECT [Loan Number], MAX([Payment Dates]) AS [Payment Dates]FROM Table GROUP BY [Loan Number]; djj
it will definitely work so far as you need to return only LoanNumber and date alone. In case you've any more columns to be returned from the latest record then you need to use my method or add a join to the above with your original table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-06-24 : 06:34:01
|
visakh16, Thank you for the information.djj |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-24 : 06:45:11
|
no problemyou're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|