Re: Sql basic Query

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
>