It isn't that easy. Try this (haven't tested it):
SELECT news1.id, news2.id, news3.id, message
FROM news AS news1, news AS news2, news AS news3
WHERE news1.id < news2.id AND news2.id > news3.id
and not exists (
select 1
from news n
where n.id > news1.id and n.id < news2.id)
and not exists (
select 1
from news n
where n.id > news2.id and n.id < news3.id)
Tommi Mäkitalo
Janning Vygen wrote:
>imagine this table:
>
>create table news (
> id SERIAL,
> newsdate date,
> message text
>);
>
>Now i want to get a result table like this
>
>id | next_id | prev_id | message
>--------------------------------
>
>So all the news should be ordered by their newsdate AND i want a
>listing for each news which comes next.
>
>I've started with something like
>
>SELECT news1.id, news2.id, news3.id, message
>FROM news AS news1, news AS news2, news AS news3
>WHERE news1.id <> news2.id AND news2.id <> news3.id AND
> news1.id <> news3.id;
>
>but how do i get only those rows which show the ids which are next to
>each other. I dont want to save an array index inside the table or
>create a tree inside the table. i just want to get it sorted by date,
>but not only vertical.
>
>later i just want to ask with php like select * news_view where id =
>5; and i can create a navigation easily just by calling myself with
>another id.
>
>i hope you understood my question
>
>regards
>janning
>
