How to get the last recored of every year in query

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

General SQL Server Forums

New to SQL Server Programming

How to get the last recored of every year in query

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-28 : 08:53:35

hello all,

i have a query in which i need to get latest record of every year i have written max statement but i am getting the last record of 2013
but i need to get last record of 2013,2012,2011,2010(single record of every year)


SELECT
Max(CASE when year(exm.entrydate) is not null then  year(exm.entrydate )END) as land,
 MAX(Case when exm.entrydate is not null then exm.entrydate END) as Yeardate,
 MAX(Case when exm.testvalue is not null then  exm.testvalue END)as 'Height'
FROM excz_measurements exm	
WHERE exm.userID = '21961'
	AND exm.testid = 5

P.V.P.MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-28 : 09:04:11

You could use the RANK() over ROW_NUMBER() function. (Both would behave the same way except, RANK would give you more than one record for a given year if there are ties)

select * from 
(	
select *,
	RANK() over (partition by year(exm.entrydate) order by exm.entrydate desc) as RN
from excz_measurements exm	
WHERE exm.userID = '21961'
	AND exm.testid = 5
) s where RN = 1;

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-28 : 09:31:39

Now the entire table list is coming in year wise i only want to get selected columns in that table

exm.entrydate,exm.testvalue for that table only this 2 columns i need to show

P.V.P.MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-28 : 09:42:41

Change the * with the columns you want

select entrydate,testvalue  from 
(	
select exm.entrydate,exm.testvalue ,
	RANK() over (partition by year(exm.entrydate) order by exm.entrydate desc) as RN
from excz_measurements exm	
WHERE exm.userID = '21961'
	AND exm.testid = 5
) s where RN = 1;

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-28 : 09:49:57

thanks james it worked a ton

P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources