Returning next row information only

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)

Returning next row information only

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-12 : 07:58:39

Jon writes "The SQL is coming from the product search page. This process is on the product details page and we are getting the “next product” (this is a link). Here is an example of what should happen.

Original SQL search.

SELECT * FROM [products] WHERE active = ‘Y’ AND aid = 3 ORDER BY sku

**Returns 10 rows

1 apple
2 banana
3 cat
4 dog
5 elephant
6 fox
7 giant
8 hipo
9 insect
10 jaguar

Let’s say the current row is 5. So I would need to return row 6 only.

Also the bonus question… let’s say the current row is 10, then row 1 would need to be returned (continually loops).

Thank you,
Jon C."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-12 : 08:34:26

The proc used to populate your details page should return the primary key of the 'next'. That way your app can create the appropriate command to call the 'next' details page.


select
    min(pk)
from
    table
where
    pk > @currentpk and
    other predicates

Jay White
{0}

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-12 : 09:13:09

declare @t table (rowno int,prodname varchar(20))
insert into @t select 1,'apple'
insert into @t select 2,'banana'
insert into @t select 3,'cat'
insert into @t select 4,'dog'
insert into @t select 5,'elephant'
insert into @t select 6,'fox'
insert into @t select 7,'giant'
insert into @t select 8,'hipo'
insert into @t select 9,'insect'
insert into @t select 10,'jaguar'
declare @rowno int
select top 1 @rowno=rowno from @t where rowno > 5

if @rowno is null
begin
select top 1 rowno from @t
end
else
begin
select * from @t where rowno=@rowno
end

V.Ganesh
NetAssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-12 : 09:17:09

Please replace the value with the variable.

declare @t table (rowno int,prodname varchar(20))
insert into @t select 1,'apple'
insert into @t select 2,'banana'
insert into @t select 3,'cat'
insert into @t select 4,'dog'
insert into @t select 5,'elephant'
insert into @t select 6,'fox'
insert into @t select 7,'giant'
insert into @t select 8,'hipo'
insert into @t select 9,'insect'
insert into @t select 10,'jaguar'
declare @rowno int
Declare @currentRowNo int
set @currentRowNo = 10
select top 1 @rowno=rowno from @t where rowno > @currentRowNo

if @rowno is null
begin
select top 1 rowno from @t
end
else
begin
select * from @t where rowno=@rowno
end

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources