Last entry for month - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Last entry for month

Author

Topic

gjja
Starting Member

17 Posts

Posted - 2013-10-17 : 14:54:34

I need a query that will return the last value (row) for each month

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-17 : 15:13:35

[code]select * from
(
select *, row_number() over (partition by datediff(mm,0,ModifyDate) order by ModifyDate desc) as RN
from YourTable
) s where RN = 1;[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-18 : 03:26:34

I see you've multiple records with same date value. In that case if you've multiple records for same max date of month do you need all to be returned? if yes, you need to tweak suggestion as


select * from 
(
   select *, dense_rank() over (partition by datediff(mm,0,ModifyDate) order by ModifyDate desc) as RNK
   from YourTable
) s where RNK = 1;

------------------------------------------------------------------------------------------------------
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