Re: Sql basic Query

From: Tommi Maekitalo <t(dot)maekitalo(at)epgmbh(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Sql basic Query
Date: 2002-03-01 13:17:10
Message-ID: 3C7F7F56.9020406@epgmbh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Nikolay Mihaylov 2002-03-01 13:38:44 Pg_dump very large database
Previous Message Jean-Michel POURE 2002-03-01 11:07:40 Re: PostgreSQL Licence: GNU/GPL