How to retrieve rows which immediately after a ID

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 retrieve rows which immediately after a ID

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-26 : 06:52:49

Hello..

Can any one you help me in retrieving the rows which immediately after a ID 9.
i.e, My condition is to retrieve the Record which immediately follows the event 9.
The retrieved record event should be in (24,25,26)

Table structure is
---------------------------------------------
MatchID SegmentID Event Player1 Player1ClubID
---------------------------------------------
1 0 9 1 88
1 0 24 1 89
1 0 10 1 88
1 0 9 1 89
1 0 25 1 88
1 0 10 1 88
1 0 10 1 89
1 0 10 1 89
----------------------------------------------

My out put should have
---------------------------------------------
MatchID SegmentID Event Player1 Player1ClubID
---------------------------------------------
1 0 24 1 89
1 0 25 1 88
---------------------------------------------

How to get this,
Can any one help me.

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 07:08:01

What else do you have to define "order of records" in your table?

E 12°55'05.25"
N 56°04'39.16"

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-26 : 07:10:20

Thanks for you reply.
I got solution.

select * into #A From
(Select Row_Number() over(order by Time asc) as RowNum,MatchID,SegmentID,Time,Event,Player1,Player1ClubID
from TrendEvents where Event IN(9,24,25,26) and MatchID=0) a

Select a.RowNum,a.MatchID,a.SegmentID,a.Time,a.Event,a.Player1,a.PLayer1ClubID
From #A a
inner join #A aa On a.rownum=aa.rownum-1 and a.Event in(24,25,26)

Ganesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 07:14:37

quote:
Originally posted by ganeshkumar08

Thanks for you reply.
I got solution.

select * into #A From
(Select Row_Number() over(order by Time asc) as RowNum,MatchID,SegmentID,Time,Event,Player1,Player1ClubID
from TrendEvents where Event IN(9,24,25,26) and MatchID=0) a

Select a.RowNum,a.MatchID,a.SegmentID,a.Time,a.Event,a.Player1,a.PLayer1ClubID
From #A a
inner join #A aa On a.rownum=aa.rownum-1 and a.Event in(24,25,26)

Ganesh

Where are you checking for condition which looks for records following event 9?

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-26 : 07:26:29

sorry make a small modification in the above query.

select * into #A From
(Select Row_Number() over(order by Time asc) as RowNum,MatchID,SegmentID,Time,Event,Player1,Player1ClubID
from TrendEvents where Event IN(9,24,25,26) and MatchID=0) a

Select a.RowNum,a.MatchID,a.SegmentID,a.Time,a.Event,a.Player1,a.PLayer1ClubID
From #A a
inner join (Select RowNum as Row from #A where Event =9) aa On a.RowNum =aa.Row+1 and a.Event in (24,25,26)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 07:34:08

quote:
Originally posted by ganeshkumar08

sorry make a small modification in the above query.

select * into #A From
(Select Row_Number() over(order by Time asc) as RowNum,MatchID,SegmentID,Time,Event,Player1,Player1ClubID
from TrendEvents where Event IN(9,24,25,26) and MatchID=0) a

Select a.RowNum,a.MatchID,a.SegmentID,a.Time,a.Event,a.Player1,a.PLayer1ClubID
From #A a
inner join (Select RowNum as Row from #A where Event =9) aa On a.RowNum =aa.Row+1 and a.Event in (24,25,26)

Yup. looks ok now. Thanks for posting the solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources