Compare the data in the 1st row with data in the 2nd row

I need to be able to compare the value of a column in row 1 of table with the value of the same column in row 2 and then row 2 with row 3 and row 3 with row 4 and so on and so. I have to be able to do this with a sql query not in a stored proc or function, etc. Any ideas?

I am using SQL Enterprise Manager Version: 8.0

How to dynamically number rows in a SELECT Transact-SQL statement

http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

Also, check IDENTITY property in BOL.

create table #t1(c1 int not null identity unique, c2 int)

insert into #t1(c2)

select c2

from your_table

order by c2

select a.c1, b.c1, a.c2, b.c2

from #t1 as a inner join #t1 as b on a.c1 = b.c1 - 1

AMB

|||

Here one more approach,

Code Snippet

Create Table #mytable (

[Data1] int ,

[Data2] Char

);

Insert Into #mytable Values('10','A');

Insert Into #mytable Values('2','B');

Insert Into #mytable Values('3','A');

Insert Into #mytable Values('45','Z');

Insert Into #mytable Values('18','B');

Insert Into #mytable Values('29','K');

--use into to store the data into Temp table

Select * into #temp from #mytable Order By data1;

--Add the Identity Column

Alter table #temp Add RowId int identity(1,1);

--Join with RowId

select

Up.*,

Down.*

from

#temp Up

Inner Join #temp Down

on Up.RowId = Down.RowId-1

|||

Hi Manivannan,

The script is ok, but it is not deterministic. You can have different result using same data, because you are inserting into #mytable with no order.

AMB

|||

Yeap.. I missed it.. Thanks for pointing that.. Fixed now..