SQL statement to get Next Record based on Field Va

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

SQL Server Development (2000)

SQL statement to get Next Record based on Field Va

Author

Topic

sunnysak
Starting Member

7 Posts

Posted - 2005-02-02 : 09:43:51

Hello Guys any help please..

1. Given a ID No. on 1st table read the NextID field and get 2nd record, read NextID of 2nd record and get the 3rd record.. as so on...

For e.g. ID= 135

Now I need all the interlinked records of 135 meaning.. look for NextID field in Record #135 say its 138 so display #138, next look for NextID field for Record #138, say it is 152 so, display 152 again look for NextID field in Record #152 say it has 238 so, dispaly #238 and so on till a NULL value is found in NextID field.

So, the o/p will be like
135
138
152
238....

I am not sure if I can achieve this with one SQL statement (maybe I am wrong). thts why I's thinking if there is any other way like stored procedures or creating views.. etc.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-02 : 09:47:58

is this a hierarchy thing?? or have i misunderstood your problem?

Go with the flow & have fun! Else fight the flow

sunnysak
Starting Member

7 Posts

Posted - 2005-02-02 : 09:56:40

Thanks, but it is more like a loop of Select Statement here below will give an idea

SELECT id, StepType, StepNextID
FROM Step_Master G
WHERE (id =
(SELECT StepNextID
FROM Step_Master H
WHERE (id = 137)))

See what I mean? so now if you put the above statement in a loop it will give me all the records that I need. the output of it is

152 Single, 238 (Id, steptype, and NextID) so the next record should be 238, Single, 239 .....

But How? I don't know :( Maybe a stored procedure? Mmm.. donno where to start.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-02 : 11:14:44

well i have no idea why would you even want to do something like that, but for each level you need another subquery.
that or a loop...

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 11:41:33

quote:
Originally posted by spirit1

well i have no idea why would you even want to do something like that, but for each level you need another subquery.
that or a loop...

Go with the flow & have fun! Else fight the flow

Seems like a loop would be the perfect fit here....maybe something like this?

declare @Id int
declare @Ptr int
declare @Limit int

create table Looplist (ID int, Ptr int)
set @Id = 0
set @Limit = 3
set @Ptr = 0
while @Id < @Limit
begin
select @Id, @Ptr FROM Table_X
insert into Looplist values(@Id,@Ptr)
-- reset
set @ID = @Ptr
end
select * from Looplist
drop table Looplist

I hope this helps...

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-02 : 12:06:35

you sure do like loops, son of Darius the Great.

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-02 : 12:08:14

SELECT [id], StepType, StepNextID
FROM Step_Master G
WHERE [id] > 137
ORDER BY [id]

???

Sample Data and expected reulsts would help

And the order of data in a database is meaningless, unless you define it

Brett

8-)

EDIT: Sorry I misread that...

Look Here

http://www.sqlteam.com/item.asp?ItemID=8866

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 12:13:05

quote:
Originally posted by spirit1

you sure do like loops, son of Darius the Great.

Code reuse

!!!

rockmoose

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 12:20:22

Uh, actually I'm a big fan of Darius' arch-enemy: Alexander the Great

...son of Philip (which is my name).

I just thought Xerxes

would sound better than "Gunny" (which is short for: Gunnery Sergeant Philip Fowler, USMC-Retired).

And yes I like loops

.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-02 : 12:42:24

well Gunnery Sergeant loose the loops. that's an order!

yeah alexander is from my part of the world (ok 400 km away but still)
he had some kick ass tactits for that time.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-02 : 13:03:34

quote:
Originally posted by spirit1

well Gunnery Sergeant loose the loops. that's an order!
yeah alexander is from my part of the world (ok 400 km away but still)
he had some kick ass tactits for that time.

Go with the flow & have fun! Else fight the flow

Aye Aye Sir!!

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-02 : 13:54:58

I wouldn't recommend looping inside SQL Server until we've the DDL for the tables and INSERT INTO statements for sample data. Sure you can solve almost anything with loops, but why would you want to suffer on performance when most things can be accomplished set-based. With the DDL and sample (plus expected result set using that sample data), we should be able to help you.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources