weird group by - 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 2005 Forums

Transact-SQL (2005)

weird group by

Author

Topic

xholax
Starting Member

12 Posts

Posted - 2013-03-18 : 19:16:32

Hello friends hope you can help me, i need the below query for my job but i cant get it :( :

--Sample Data
DECLARE @Foo TABLE (oper CHAR(10), [start] int, [end] int , [status] int )

INSERT @Foo
select 'James', 0,3,2 union all
select 'James', 3,5,2 union all
select 'James', 5,8,3 union all
select 'James', 8,9,4 union all
select 'James', 9,10,2 union all
select 'James', 10,14,2 union all
select 'James', 14,48,3

select * from @Foo

thanks in advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-18 : 19:41:22

[code];with cte as
(
select *,
ROW_NUMBER() over (order by start,[end])-
ROW_NUMBER() over (PARTITION by status order by start,[end]) Grp
from
@Foo
)
select
oper,
MIN(start) [start],
MAX([end]) [end],
status
from
cte
group by
oper,
status,
grp
order by
[start],
[end][/code]

xholax
Starting Member

12 Posts

Posted - 2013-03-18 : 23:05:13

thanks it works perfect, i didnt know about that kind of query partitions and rank where i can find more info about that , some recommend book ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources