question

ss2k avatar image
ss2k asked

Return recordset of ranges found in a list

I'm looking for a cursor free solution compatible with SQL2005 to return start and end values of the range found or just a start value for a single value. Thanks.

I suspect a WITH CTE and perhaps a windowing function are the key to this, but I'm still new to them.

Here's a sample list and what I'd like to achieve:

SET NOCOUNT ON

CREATE TABLE #lists ( [id] [int] NOT NULL CONSTRAINT [PK_lists] PRIMARY KEY CLUSTERED ( [id] ASC ) )

CREATE TABLE #expectedoutput ( [start] [int] NOT NULL, [end] [int] NULL )

insert into #lists select 1

insert into #lists select 2

insert into #lists select 3

insert into #lists select 5

insert into #lists select 6

insert into #lists select 9

insert into #lists select 12

insert into #expectedoutput select 1,3

insert into #expectedoutput select 5,6

insert into #expectedoutput select 9,null

insert into #expectedoutput select 12,null

select * from #lists

select * from #expectedoutput

drop table #lists

drop table #expectedoutput

sql-server-2005
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Squirrel 1 avatar image
Squirrel 1 answered 
select  min(id), max(id)            
from            
(            
    select  id, grp = dense_rank() over(order by id - row_no)            
    from                
    (            
            select  *, row_no = row_number() over (order by id)            
        from    #lists            
    ) l            
) l            
group by grp

or to get the exact of your required output, check for max(id) if not equal to the min, return NULL

select  min(id), case when min(id) <> max(id) then max(id) end            
from            
(            
    select  id, grp = dense_rank() over(order by id - row_no)            
    from                
    (            
            select  *, row_no = row_number() over (order by id)            
        from    #lists            
    ) l            
) l            
group by grp
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.