How to compare values in different rows?

Hi

I’ve a table like this (in SQL Server 2000).

v1 v2
2 8
7 10
11 15

13 17

v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.

please advise

Thanks

In order to best get this to work you need another row to explicitly sequence the data -- such as ROW_ID or something. The otherwise, you can use Transact SQL extensions, but without an explicit sequence of some kind, the results will be volatile / unpredictable. It would probably be best to avoid the extensions and stick to a CTE here if you can.

Code Snippet

declare @.ex table(id int, v1 int, v2 int)
insert @.ex
select 1, 2, 8 union all
select 2, 7, 10 union all
select 3, 11,15 union all
select 4, 13,17
--select * from @.ex

select a.id, a.v1, a.v2
from @.ex a
join @.ex b
on a.id - 1 = b.id
and a.v1 <= b.v2

/*
id v1 v2
-- -- --
2 7 10
4 13 17
*/

|||

Do you have a field in the table that will indicate what is the 'previous row'? (Something like an IDENTITY field, or datetime of entry, etc.)

|||

The Table is

ID v1 v2
1 2 8
2 7 10
3 11 15

4 13 17

|||

Here it is,

Code Snippet

Create Table #data (

[ID] INT ,

[v1] INT ,

[v2] INT

);

Insert Into #data Values('1','2','8');

Insert Into #data Values('2','7','10');

Insert Into #data Values('3','11','15');

Insert Into #data Values('4','13','17');

Select

Down.*

from

#data Up

Join #data down On Up.Id = Down.Id-1

Where

Up.V2>=down.V1

|||

As long as you have that ID field, you can use the query

Code Snippet

CREATE TABLE prevRowTest (

ID INT,

v1 INT,

v2 INT

)

INSERT INTO prevRowTest VALUES (1,2,8)

INSERT INTO prevRowTest VALUES (2,7,10)

INSERT INTO prevRowTest VALUES (3,11,15)

INSERT INTO prevRowTest VALUES (4,13,17)

SELECT prt.ID, prt.v1, prt.v2

FROM prevRowTest prt JOIN

prevRowTest prev ON prt.id = (prev.ID + 1)

WHERE prt.v1 <= prev.v2

|||

Thanks a lot.

I got my Answer

With Regards

Vijay