Compare Rows in SQL Server

In this post, i would like to give a simple tip on comparing rows in SQL Server.

Let's create "COMPARE" table and insert the following data.

Create Table Compare(name varchar(10),reading int,sq_no int identity(1,1))

insert into Compare (name , reading) select 'UNDRI',10
insert into Compare (name , reading) select 'UNDRI',11
insert into Compare (name , reading) select 'UNDRI',20
insert into Compare (name , reading) select 'UNDRI',20
insert into Compare (name , reading) select 'UNDRI',20
insert into Compare (name , reading) select 'UNDRI',22
insert into Compare (name , reading) select 'UNDRI',45
insert into Compare (name , reading) select 'UNDRI',20

This table has 3 column Name, reading and sq_no. Now the task is, i need to compare first row column value in reading with second row column value in reading, and Second row column value in reading with Third row column value in reading so on..

The simple rechnique with out modifying the table structure is:
Create a view with an additional column which has the unique row number to compare with.

CREATE VIEW v_compare as
SELECT o.*,(SELECT MAX(sq_no) FROM compare i WHERE I.SQ_NO <>

Now the next step is using the below query to find out the difference between the rows.

SELECT o.name,o.reading,(select o.reading-i.reading from compare i where i.sq_no = o.pre_sq_no) AS RESULT
FROM V_compare o