> sorry for being unclear.
>
> but you guessed right. ID is UNIQUE and and I want to select a row by
> its ID and also get the previous and next ones in the name, name2-order.
>
> For the selected row I need all datafields and for the next and previous
> I need only the ID (to have it referenced on the dataoutputpage for a
> certain row).
OK, this is a lot clearer now.
I suppose you have a UNIQUE(name,name2) or else, if you have several rows
with the same (name,name2) you'll get one of them, but you won't know
which one.
For example :
select * from test;
id | name | name2
----+------+-------
1 | a | a
2 | a | b
3 | a | c
4 | b | a
5 | b | b
6 | b | c
7 | c | a
8 | c | b
9 | c | c
(9 lignes)
Solution #1 :
- In you application :
SELECT * FROM test WHERE id=4;
id | name | name2
----+------+-------
4 | b | a
You then fetch name and name2 and issue the two following SELECT,
replacing 'a' and 'b' with name2 and name1 :
SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY
name,name2 ASC LIMIT 1;
id | name | name2
----+------+-------
5 | b | b
SELECT * FROM test WHERE (name='b' and name2<'a') OR (name<'b') ORDER BY
name,name2 DESC LIMIT 1;
id | name | name2
----+------+-------
3 | a | c
These should use an index on (name,name2).
Solution #2 :
You could do the same in a pl/pgsql function, which will be a lot faster,
and return three rows.
It is a pity you cannot use (name,name2) > ('a','b').
