Help needed with T-SQL query - 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 2008 Forums

Transact-SQL (2008)

Help needed with T-SQL query

Author

Topic

reddy_vam
Starting Member

43 Posts

Posted - 2013-04-06 : 17:14:11

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

545 Posts

Posted - 2013-04-07 : 08:47:32

;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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources