Hi
I’ve a table like this (in SQL Server 2000).
v1 v2
2 8
7 10
11 1513 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 154 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
