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..
