Posted by Stefan Rybacki on 07/24/05 14:51

Hilarion wrote:
>>>> 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.
>

yes sorry, didn't recognized this case. Whatever adding "AND id <> previousid" should do
the trick or the use of your queries ;)

>
>...
>
> 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.
>
>

Thats fine.


> Regards
> Hilarion

Regards
Stefan

[Back to original message]