How to compare previous row with the next row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to compare previous row with the next row

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-09-10 : 02:01:17

I want to know is there any way to compare the previous row with the next row in QUERY. I mean SELECT statement. if any one knows please tell me.

Thanks in advance

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 02:09:17

if you have an identity key then you can compare the data using this key, set a variable to a key and subtract or add 1 to get previous or current, which ever you like.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-10 : 02:23:54

jen is right, SQL has no concept of order of records, except by an ID field, or some other way of sorting. The order of the records is irrelevant, unless you are sorting them by some value.

Anyhow, here's some example code which might get you going:

create table compare (id int, value varchar(10))
insert into compare (id, value) values (1, 'b')
insert into compare (id, value) values (2, 'b')
insert into compare (id, value) values (3, 'a')
insert into compare (id, value) values (4, 'z')

select a.value, 
	case 
	when a.value < b.value then 'less than'
	when a.value > b.value then 'greater than'
	else 'equal'
	end,
	b.value
from compare a, compare b
where a.id - 1 = b.id
order by a.id

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-09-10 : 02:24:50

But I don't have any identity column.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-10 : 02:27:30

then you have no way of knowing which row is which. What other columns do you have? You don't necessarily need to have an identity column, but you must have some value which tells you which "order" the records are in.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 02:36:16

your last resort is to use a cursor

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-10 : 02:36:56

can you push data in a temp table that has an extra column(identity). then work on that temp table

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-10 : 02:40:36

Another question why you want to compare that?

mk_garg

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-10 : 02:40:43

if you can't change your table design then i'd go for this solution

quote:
Originally posted by mk_garg20

can you push data in a temp table that has an extra column(identity). then work on that temp table

mk_garg

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-09-10 : 02:45:58

Yes i agree with you jen.

Yeah according to Databse concept all tables should have primary keys.
But Sometimes we dont have that luxury, particular when you inherit poorly designed databases.

quote:

your last resort is to use a cursor

I guess using table variable(SQL Server 2000) or temp table will be fatser than cursor.

All Gurus please correct me if i am wrong.

mk_garg

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-10 : 03:49:54

no, no - the point is not to use a cursor. The point is to work out what you mean by "previous row". If you can't define what orders one rowafter the other, you have larger problems than deciding on an iterative vs set-based solution.

You must know what the basis of the ordering is, and therefore, you have some basis for making the comparison ala the suggested code above...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources