We help IT Professionals succeed at work.

select next row using value from column as reference

hi, i have an table of the bible, so we have this columns:

id      idbook       idchapter
1         1                 1
2         1                  2
3          1                3
4          2                 1

now, how do i build an select query to select the row after the

book 1 chapter 3  =  book 2 chapter 1
book 1 chapter 2  = book 1 chapter 3

any help?
Comment
Watch Question

Christopher GordonSenior Developer Analyst

Commented:
Just add 1 to the Id column and join the table to itself.  This assumes the table is in order based on the ID column.  If it's not, i'll post some alternate code.  Note:   just paste below into ssms.
declare @myTable table (id int identity(1,1), idbook int, idchapter int)

insert into @myTable values(1,1)
insert into @myTable values(1,2)
insert into @myTable values(1,3)
insert into @myTable values(2,1)


select		current_row.*
		,	next_row.idbook as Next_Idbook 
		,	next_row.idchapter as Next_IdChapter
		
from	@myTable current_row 

left outer join @myTable next_row on
	(current_row.Id + 1) = next_row.Id

Open in new window

Senior Developer Analyst
Commented:
Here's an alternate version that ignores the ID column and creates an alternate ID column called "instance counter" in the order of idbook and idchapter.  Same logic though,  your joining the table to itself on the ID column and adding 1 to  it in order to get the next row of data.
declare @myTable table (id int identity(1,1), idbook int, idchapter int)

insert into @myTable values(1,1)
insert into @myTable values(1,2)
insert into @myTable values(1,3)
insert into @myTable values(2,1)


;with MyTableWithInstances as
(
	--get an ordered number in ascending order by book and chapter for join.
	select *
		, ROW_NUMBER() over (Order By idbook, idchapter) as Instance_Counter
	from @myTable
) 



select		current_row.id
		,	current_row.idbook
		,	current_row.idchapter
		,	next_row.idbook as Next_Idbook 
		,	next_row.idchapter as Next_IdChapter
		
from	MyTableWithInstances current_row 

left outer join MyTableWithInstances next_row on
	(current_row.Instance_Counter + 1) = next_row.Instance_Counter

Open in new window

Commented:
try this
Select 
A.id, A.idbook, A.idchapter,
B.id, B.idbook, B.idchapter
From bible A left Outer Join bible B
on (A.idbook = B.idbook and B.idchapter = A.idchapter-1 and A.idchapter<> 1)
or  (B.idbook = A.idbook-1 and A.idchapter = 1 and B.idchapter = (Select Max(idchapter) From bible c where B.idbook = C.idbook ))

Open in new window

Author

Commented:
any way to make this an function that returns 2 values one for idbook, and the other for idchapter.

so i can call it like this

select * dbo.getnexchapter(idbook, idchapter) return table (idnextbook, idnexchapter)
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
CREATE FUNCTION dbo.getnextchapter (
    @idbook int,
    @idchapter int
    )
RETURNS TABLE AS
RETURN (
SELECT TOP 1
    idbook, idchapter
FROM tablename
WHERE
    (idbook = @idbook AND idchapter = @idchapter + 1) OR
    (idbook = @idbook + 1 AND idchapter = 1)
ORDER BY idbook
)
GO

Explore More ContentExplore courses, solutions, and other research materials related to this topic.