[thelist] An sql problem

[Joshua OIson]

What about this to select the previous

SELECT id
FROM table
WHERE ref < [thisone.ref]
ORDER BY ref DESC

where [thisone.ref] is the ref of the one you are currently viewing. Then
make sure that the query is set up to retreive only one row.

Then use this for the next:

SELECT id
FROM table
WHERE ref > [thisone.ref]
ORDER BY ref ASC

And again, make sure the query is set up to only return one row.

In ColdFusion you'd use the maxrows option. In ASP, PHP, etc, I don't know.

-joshua

----- Original Message -----
From: "Philippe Jadin" <philippe.jadin at 123piano.com>
Subject: [thelist] An sql problem


> Hello,
>
> I'm facing a stupid problem in sql, and I thought someone could help here
:
>
> I have a table with those :
>
> ID | ref | cat | blabla...
>
> I'd like to add "next" and "previous" buttons on a page displaying a
> particlar row of the table.
>
> What sql sould I use to get the next and previous id's based on a
particular
> "id", and ordered in this case by "ref"? I obviously can't say next = id+1
> and previous=id-1. Because Id's are not +1 for each row.
>
> I would like to have somethin like this
>
> id|ref|cat
> 22|po1|1 ->the previous id
> 51|po2|1 -> the current id
> 12|po3|1 ->the next id