Posted by Hilarion on 07/23/05 19:03

>>> Maybe this way:
>>>
>>> previous:
>>> SELECT * FROM adresse WHERE nom<='Dupont' AND id<>123 ORDER BY nom
>>> DESC LIMIT 1
>>>
>>> next:
>>> SELECT * FROM adresse WHERE nom>='Dupont' AND id<>123 ORDER BY nom ASC
>>> LIMIT 1
>>
>>
>> This will work OK only if "nom" values are unique. If they are not, then
>> you'll
>> always get other "duplicate" of current record as previous and as next.
>> In that case you should use something like:
>>
>
> No, thats wrong. Since I used the constraint of id<>123!

You are wrong. If there are two "Dupont" entries, one with ID 123 and
another with for example 321 then your "previous" select would return
the record with ID 321, and your "next" select would also return
ID 321. If the current record was 321, then "previous" and "next"
would return 123.


>> previous:
>> SELECT *
>> FROM adresse
>> WHERE (nom < 'Dupont') OR (nom = 'Dupont' AND id < 123)
>> ORDER BY nom DESC, id DESC
>> LIMIT 1
>
> Who says that the previous item has an id that is lower than the current id?

I said. To make "next" and "previous" work one have to decide what is the
order of records which identical "nom" value. I decided that I'll use
ID in ascending order. I also wrote that:
'You could use some another column or columns (the whole set of coulmns used
has to give unique entries). The more columns you use, the more complex the
query gets.'
What I did not wrote is that you can choose ordering of those column values
as you want. It was easier for me to use same ordering direction as for
"nom" column.


>> next:
>> SELECT *
>> FROM adresse
>> WHERE (nom > 'Dupont') OR (nom = 'Dupont' AND id > 123)
>> ORDER BY nom ASC, id ASC
>> LIMIT 1
>
> same here.

Same here.


Regards
Hilarion

[Back to original message]