How to check Previous and next record values

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 2005 Forums

Transact-SQL (2005)

How to check Previous and next record values

Author

Topic

HelenRose
Starting Member

3 Posts

Posted - 2008-05-22 : 02:31:11

Hi all,

I wanted to check the previous and next record values.

For example:

sKey NextKey PreviousKey

1 2 Null
2 8 1
8 5 2
5 null 8

I wanted to check the value of NextKey of Prev record and Skey of Next record.

Any idea?

Regards
Helen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-22 : 02:40:00

please explain more of what you are doing.

HelenRose
Starting Member

3 Posts

Posted - 2008-05-22 : 02:46:30

Hi,

Ex : In the first record of the table, the NextKey is pointing to 2.
So the next record of Skey will be 2. The Next Key for this record is 8. Like wise the next record of this should have the Skey as 8.

Now I need to check whether the NextKey and SKey are correct for all rows.

For that I need to check the previous record of "Next key" and next record of "Skey".

Regards
Helen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-22 : 03:00:23

[code]
select *
from
(
select a.*,
case
when a.NextKey is null then 1
when a.skey = (select aa.PreviousKey from Mytable aa where aa.sKey = a.Nextkey) then 1
when else 0
End as Check1,
case
when a.PreviousKey is null then 1
when a.skey = (select aa.NextKey from Mytable aa where aa.sKey = a.Previouskey) then 1
when else 0
End as Check2
From
Mytable a
) ab
where ab.Check1 = 0 or ab.check2 =0

[/code]

This will return any rows that don't check out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 03:04:12

quote:
Originally posted by HelenRose

Hi,

Ex : In the first record of the table, the NextKey is pointing to 2.
So the next record of Skey will be 2. The Next Key for this record is 8. Like wise the next record of this should have the Skey as 8.

Now I need to check whether the NextKey and SKey are correct for all rows.

For that I need to check the previous record of "Next key" and next record of "Skey".

Regards
Helen

Do you have any other column in your table? May be a PK column?

HelenRose
Starting Member

3 Posts

Posted - 2008-05-22 : 04:37:12

Hi,

Thanks for your reply.
Skey is PK column.

Regards
Helen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources