asked on

Need to select a field that is listed after another

I have a query that outputs data similar to the following

ID Order Page
10 1 Page1
20 2 Page2
30 3 Page3

I want to add a few fields to the query so I get something like the following:
ID Order Page NextPage PrevPage
10 1 Page1 Page2 Page3
10 2 Page2 Page3 Page1
10 3 Page3 Page1 Page2

The pages are not named so easily so I can't query against the page names. I really need to read ahead and see what the next page is so I can put it in the NextPage field. Simliar issue with the PrevField, I need to read the previous record and get its page name to put it in the PrevPage field.

I would like to stay away from temp tables but if that is the only solution I would take it.

I am using it for navigation

nigelrivett

Easiest is probably to put the results of that query into a temp table

insert #a
exec spget

select id, order, page,
nextpage = (select min(a2.page) from #a a2 where a2.order > #a.order)
prevpage = (select max(a3.page) from #a a3 where a3.order < #a.order)
from #a

For the max and min orders probably eaiest to get the max and min values before the query and use a case statement.

nextpage = case when #a.order = @maxpage then @minpage else (select min(a2.page) from #a a2 where a2.order > #a.order) end

or you can inorporate this sort of thing into the query.

nigelrivett

Noticed you want to stay away from temp tables.
You can probably incorporate this into your query in that case.

dtomyn

Could you clarify what you mean by "I am using it for navigation"? That is, I am thinking there is a better overall solution than having to do this. ex. Is this for web development?

kkirt1

ASKER

Yes, this is web development. I have navigation on each of my pages that run through a list of input pages that is dynamically driven from the DB.

nigelrivett: How would the SQL statement look?

dtomyn

Ok.

To expand on nigelrivett's comment then, here is a sample script to do what you want:

DROP TABLE SomeTable
GO

CREATE TABLE SomeTable
([ID] INT, [ORDER] INT, [PAGE] VARCHAR(50))
GO

INSERT INTO SomeTable
([ID], [ORDER], [PAGE])
VALUES
(10, 1, 'Page 1')

INSERT INTO SomeTable
([ID], [ORDER], [PAGE])
VALUES
(20, 2, 'Page 2')

INSERT INTO SomeTable
([ID], [ORDER], [PAGE])
VALUES
(30, 3, 'Page 3')

SELECT
[ORDER], [PAGE]
, ISNULL(
(SELECT MIN(a2.[PAGE])
FROM SomeTable a2 where a2.[order] > a.[order])
, (SELECT MIN(a2.[PAGE])
FROM SomeTable a2 WHERE a2.[order] <> a.[order])) AS 'NextPage'
, ISNULL(
(SELECT MAX(a2.[PAGE])
FROM SomeTable a2 where a2.[order] < a.[order])
, (SELECT MAX(a2.[PAGE])
FROM SomeTable a2 WHERE a2.[order] <> a.[order])) AS 'PrevPage'
FROM SomeTable a

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial