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
 How to get the last recored of every year in query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/28/2013 :  08:53:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 03/28/2013 :  09:04:11  Show Profile  Reply with Quote
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;
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/28/2013 :  09:31:39  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 03/28/2013 :  09:42:41  Show Profile  Reply with Quote
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;
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/28/2013 :  09:49:57  Show Profile  Reply with Quote
thanks james it worked a ton

P.V.P.MOhan
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.19 seconds. Powered By: Snitz Forums 2000