Help for Sql Union

Question

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 request data and 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 a direction, LowNum, and then nextNumber. Also, when the direction changes to start over. (That's why I think I need a Union, maybe the North, and then the Union South). Also note that the largest number in each direction does not create a record because there is no larger 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. It is also a SQL 2008 database, so I can use 2008 TSQL. Do I need a cursor here? Is the best solution to use C # and Linq? 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) 

Interestingly, at the moment there are 3 different answers, each of which has different characteristics.

Answer 3

This will be good

 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

In 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 with other solutions that I have not researched.