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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help needed with T-SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reddy_vam
Starting Member

43 Posts

Posted - 04/06/2013 :  17:14:11  Show Profile  Reply with Quote
Here is my test table

id month sales
1 Jan 1000
1 Feb 2000
2 April 200
2 May 500
2 June 800
3 Jan 4000
3 Feb 2000
3 April 100

Exepected output should be like below

id month sales
1 Feb 2000
2 June 800
3 Jan 4000

Please help me with a T-SQL query for the above output

Thanks,
Reddy

stepson
Constraint Violating Yak Guru

Romania
428 Posts

Posted - 04/07/2013 :  08:47:32  Show Profile  Reply with Quote
;with Sales
AS
(
select 1 as ID, 'Jan' as [month], 1000 as sales
union all
select 1 ,'Feb' , 2000
union all
select 2 ,'April', 200
union all
select 2 ,'May' ,500
union all
select 2 ,'June', 800
union all
select 3 ,'Jan' ,4000
union all
select 3 ,'Feb' ,2000
union all
select 3, 'April', 100
)




select S.* 
from Sales as S
	outer apply
	(
		select top 1 
			id
			,[month] as[month]
			,sales
		From 
			Sales as SS
		where 
			S.ID=SS.id
		order by SS.sales desc
	)	SR
where S.id=SR.id and S.[month]=SR.[month]
order by S.ID 



S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
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.03 seconds. Powered By: Snitz Forums 2000