Sql Union Query Help

Question

Hi All, I need a little help writing a query. I have this data...

vDir    iNumber
North   19
North   27
North   29
North   31
South   46
South   49
South   51
South   61

I need to query the data and have an output something like this

vDir    iLowNumber    iHiNumber
North   19            27
North   27            29
North   29            31
South   46            49
South   49            51
South   51            61

The idea here is to select the direction, the LowNum and then the nextNumber. Also when the direction changes to start over. (Thats why I think I need a Union, maybe get the North, then Union the South). Notice too the highest number in each direction doesn't create a record because there is no higher number. The first set has 8 records, and the query result is only 6. How can I create a query that will do this? Any help is appreciated. Also this is a 2008 SQL Database so I can use 2008 TSQL. Do I need a cursor here? Is a better solution to use c# and Linq perhaps? I'm really curious how to do this in SQL. Thanks guys!

Cheers, ~ck in San Diego

Answer 1

Another possible solution:

SELECT
    T1.vDir,
    T1.iNumber AS iLowNumber,
    T2.iNumber AS iHiNumber
FROM
    My_Table T1
INNER JOIN My_Table T2 ON
    T2.vDir = T1.vDir AND
    T2.iNumber > T1.iNumber
LEFT OUTER JOIN My_Table T3 ON
    T3.vDir = T1.vDir AND
    T3.iNumber > T1.iNumber AND
    T3.iNumber < T2.iNumber
WHERE
    T3.vDir IS NULL  -- If this is NULL it means that no rows exist between T1 and T2

Answer 2

    select a.vDir, 
           a.iNumber as iLowNumber, 
           b.iNumber as iHiNumber 
      from TheTable a
inner join TheTable b on a.vDir = b.vDir
                  and a.iNumber < b.iNumber 
                  and not exists(select 1 
                                   from TheTable c 
                                  where a.vDir = b.vDir
                                    and a.iNumber < c.iNumber 
                                    and c.iNumber < b.iNumber)

Interesting that there are 3 different answers so far, each with different performance characteristics.

Answer 3

This will do it well

select * from(
 select direction,inumber as low,
 (select top(1) inumber  from cte as b where b.direction=a.direction
 and b.INumber>a.inumber) as high
 from cte as a
 ) as x where high is not null

Answer 4

TO my mind the most natural solution is:

select t1.vdir, t1.inumber as lownumber, min(t2.inumber) as highnumber
from mytable t1
join mytable t2 on t2.vdir=t1.vdir
  and t2.inumber>t1.inumber
group by t1.vdir, t1.inumber
order by t1.vdir, t1.inumber

How the performance of this compares to other solutions I haven't investigated.