Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Aged Yak Warrior

Romania
545 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  
 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.06 seconds. Powered By: Snitz Forums 2000