Most recent date for each group

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

SQL Server 2008 Forums

Transact-SQL (2008)

Most recent date for each group

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2013-06-21 : 08:56:08

Hi

I'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 Dates
1 10/20/2010
1 10/20/2011
1 10/20/2012
1 10/20/2013
2 7/27/2010
2 7/27/2011
2 7/27/2012
2 7/27/2013

Desired Results
Loan Number Payment Dates
1 10/20/2013
2 7/27/2013

Thanks

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
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://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 MVP
http://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 problem

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources