Next/Prev record - SQL Server Forums

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

Transact-SQL (2000)

Next/Prev record

Author

Topic

BCrowe
Starting Member

23 Posts

Posted - 2004-09-15 : 17:12:40

Given the simplified table:

CREATE TABLE @Product (
GroupID integer,
ProductID integer
)

What is the best method to obtain the next and previous records given a GroupID value and a ProductID value when the table is sorted by GroupID, ProductID.

P.S. I would like to allow wrapping last to first and vice-versa

B Crowe

BCrowe
Starting Member

23 Posts

Posted - 2004-09-15 : 18:25:18

I think i've figured it out...if anyone has a better solution let me know please.

CREATE TABLE #PRODUCT (
GroupID integer,
ProductID integer
)

DECLARE @grp integer
DECLARE @type integer
DECLARE @nextgrp integer
DECLARE @prevgrp integer
DECLARE @nexttype integer
DECLARE @prevtype integer

SET @grp = 1
SET @type = 1

IF @type = (SELECT MAX(ProductID) FROM #Product WHERE GroupID = @grp)
BEGIN
IF @grp = (SELECT MAX(GroupID ) FROM PRODUCT)
SET @nextgrp = (SELECT MIN(GroupID ) FROM #PRODUCT)
ELSE
SET @nextgrp = (SELECT MIN(GroupID ) FROM #PRODUCT WHERE GroupID > @grp)
SET @nexttype = (SELECT MIN(ProductID) FROM #Product WHERE GroupID = @nextgrp)
END
ELSE
BEGIN
SET @nextgrp = @grp
SET @nexttype = (SELECT MIN(ProductID) FROM #Product WHERE ProductID> @type AND GroupID = @grp)
END

IF @type = (SELECT MIN(ProductID) FROM #Product WHERE GroupID = @grp)
BEGIN
IF @grp = (SELECT MIN(GroupID ) FROM #Product )
SET @prevgrp = (SELECT MAX(GroupID ) FROM #Product )
ELSE
SET @prevgrp = (SELECT MAX(GroupID ) FROM #Product WHERE GroupID < @grp)
SET @prevtype = (SELECT MAX(ProductID) FROM #Product WHERE GroupID = @prevgrp)
END
ELSE
BEGIN
SET @prevgrp = @grp
SET @prevtype = (SELECT MAX(ProductID) FROM #Product WHERE GroupID = @prevgrp AND ProductID< @type)
END

B Crowe

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-16 : 04:57:14

doesn't this do the same?


declare @Product TABLE (
GroupID integer,
ProductID integer
)
insert into @Product
select 1, 1 union all
select 1, 2 union all
select 1, 4 union all
select 1, 5 union all
select 2, 3 union all
select 2, 5 union all
select 2, 54 

declare @GroupID int, @ProductID int
select @GroupID = 1, @ProductID = 4

select top 1 * from @Product 
where GroupID = @GroupID and ProductID < @ProductID
order by GroupID desc, ProductID desc

select top 1 * from @Product 
where GroupID = @GroupID and ProductID > @ProductID

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

BCrowe
Starting Member

23 Posts

Posted - 2004-09-16 : 09:01:41

Actually no, it doesn't handle the "edge" cases where you are at the first product of a group or the last product of a group.

B Crowe

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-16 : 12:44:04

Alternative, must be better ways than this :)

set nocount on

create table #Product(
GroupID integer,
ProductID integer )

insert into #Product
select 1, 1 union all
select 1, 2 union all
select 1, 4 union all
select 1, 5 union all
select 2, 3 union all
select 2, 5 union all
select 2, 54 

declare @GroupID int, @ProductID int
select @GroupID = 2, @ProductID = 3


declare @prevrank int, @currank int, @nextrank int

select
	@currank = ( 	select count(*) as Rank from #Product p2
			where p1.GroupID > p2.GroupID or ( p1.GroupID = p2.GroupID and p1.ProductID >= p2.ProductID ) ),
	@prevrank = case when @currank > 1 then @currank - 1 else t.maxrank end,
	@nextrank = case when @currank < t.maxrank then @currank + 1 else 1 end
from
	#Product as p1
	join ( select count(*) as maxrank from #Product ) as t on 1 = 1
where
	p1.GroupID = @GroupID and p1.ProductID = @ProductID


select
	GroupID,
	ProductID
from
	( select
		p1.GroupID,
		p1.ProductID,
		rank =(	select count(*) as Rank from #Product p2
			where p1.GroupID > p2.GroupID or ( p1.GroupID = p2.GroupID and p1.ProductID >= p2.ProductID ) )
	from
		#Product as p1 ) p
where
	p.rank = @prevrank
	or p.rank = @currank
	or p.rank = @nextrank
order by
	1,2

--select * from #Product order by 1,2
drop table #Product

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-17 : 11:53:16

Although it's not pretty, I think what you have is about as efficient as you are going to get. Depending on the indexes you have (if any) you will end up doing a couple of table scans. That could be turned into index scans if you had proper indexes but rockmoose's corelated subqueries will require, oh, 100 times more reads.

For something a little more obfuscated than yours you can try this. It will require only a single table scan, but you take your chances asking someone to maintain it.


set nocount on
create table #Product(
GroupID integer,
ProductID integer )

insert into #Product
select 1, 1 union all
select 1, 2 union all
select 1, 4 union all
select 1, 5 union all
select 2, 3 union all 
select 2, 5 union all
select 2, 54 


DECLARE @grp integer
DECLARE @type integer
DECLARE @lastgrp integer
DECLARE @lasttype integer
DECLARE @nextgrp integer
DECLARE @prevgrp integer
DECLARE @nexttype integer
DECLARE @prevtype integer
DECLARE @firsttype int
DECLARE @firstgrp int

select @grp = 2, @type = 3

select @firsttype = case when @firsttype is null then ProductID else @firsttype end,
       @firstgrp =  case when @firstgrp is null then GroupID else @firstgrp end,

       @prevgrp =   case when @type = ProductID  and @grp = GroupID  and @prevgrp  is null then @lastgrp else @prevgrp end ,
       @prevtype =  case when @type = ProductID  and @grp = GroupID  and @prevtype is null then @lasttype else @prevtype end,

       @nextgrp =   case when @type = @lasttype and @grp = @lastgrp and @nextgrp  is null then GroupID else @nextgrp end ,
       @nexttype =  case when @type = @lasttype and @grp = @lastgrp and @nexttype is null then ProductID else @nexttype end,

       @lastgrp =  GroupID,
       @lasttype = ProductID

FROM #product
ORDER BY GroupID, ProductID


select  case when @prevgrp is null then @lastgrp else @prevgrp end prevgrp,
	case when @prevtype is null then @lasttype else @prevtype end prevtype,
	@grp grp,
	@type type,
	case when @nextgrp is null then @firstgrp else @nextgrp end nextgrp ,
	case when @nexttype is null then @firsttype else @nexttype end nexttype

drop table #Product

(Edit: Bug - changed first to last...)
--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

BCrowe
Starting Member

23 Posts

Posted - 2004-09-17 : 22:25:39

Thank you all for your responses. You guys sure have a lot of spare time on your hands

B Crowe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources