|
|
Posted by Tim Roberts on 10/21/05 08:50
"Simon" <spambucket@example.com> wrote:
>Hi,
>
>If I have a lot of articles, all with a unique IDs.
>
>First I would like to search for that ID, I could do
>
>SELECT * from ARTICLE where ID = xx
>
>But I also want to display the 5 articles before and after that article.
>
>SELECT * from ARTICLE where ID > xx LIMIT 0, 10
>and
>SELECT * from ARTICLE where ID < xx LIMIT 0, 10
This is incorrect. SQL tables have no inherent ordering. Thus, the first
query will get 10 records with ID numbers larger than xx, but there is
absolutely no guarantee that they will be the records immediately above xx,
nor that they will be ordered in any way.
Given the limitations you described, there is no reliable way to do this
other than:
SELECT * FROM article WHERE ID < xx ORDER BY id DESC LIMIT 10
UNION
SELECT * FROM article WHERE ID > xx ORDER BY id LIMIT 10;
--
- Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.
Navigation:
[Reply to this message]
|