From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: QdlatY <qdlaty@wielun.dhs.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: difficult query (for me)
Date: 29 Dec 2001 11:05:40 -0500
Message-ID: <1009641941.1054.2.camel@xyzzy> (raw)
In-Reply-To: <1132751000.20011229162035@wielun.dhs.org>
References: <1132751000.20011229162035@wielun.dhs.org>
On Sat, 2001-12-29 at 10:20, QdlatY wrote:
> Hello
>
> I will try to explain my problem as clearly as i can.
>
> So, I have tree tables
> Named:
>
> Tables
> Threads
> Posts
>
> It is typicall structure of discussion phorum.
There is no such thing as typical. To help you, we need the exact
schema of your database. From the above, it's not clear what the
relation between Tables and the rest of your database is.
> Now, Tables has a column ID (unique random index)
random? Aren't you using the SERIAL type, since this is exactly the
kind of thing it's made for?
> Threads has a columns ID (index) and TableID (index of one row of tables
> table)
> Posts has attribute ThreadId and CreateTime
>
> (i only wrote important columns to this query)
>
> Now, I have Tables row with ID = 10
> And then i want to have list of all Threads (belongs to Table with id = 10,
> so WHERE Threads.TableId = 10) sorted by CreateTime of newest Posts row
> belong to every thread.
Sounds like you want to use a join with DISTINCT ON the thread id and
the ORDER BY clause to sort things by create time.
SELECT DISTINCT ON (t.id) * -- or you could list the columns you want
FROM threads t, posts p
WHERE t.tableid = 10 AND t.id = p.threadid
ORDER BY t.id, p.createtime DESC
If this will be a common query then you might consider creating an index
or two on the tables involved.
> F.E.
> Table.iD = 10
>
> Thread
> ID TableID
> 1 10
> 2 10
> 3 11
>
> Posts
> ThreadId CreateTime (i will use nubers only to explain)
> 1 10
> 1 20
> 1 25
> 2 43
> 3 02
>
>
> So i want to have result from Table ID = 10:
> Threads
> ID
> 2 (because CreateTime of Posts from 2 Thread is highest)
> 1 (and so on...)
>
> (Thread 3 not belongs to Table.ID = 10)
>
> Of course result can contains adequate Posts columns but don't must to...
>
> I hope you will able to help me, i think it's not difficult but i'm a
> little newbie, so, i gave up on this :PPP
Hope this helps.
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: drew@xyzzy.dhs.org, qdlaty@wielun.dhs.org
Subject: Re: difficult query (for me)
In-Reply-To: <1009641941.1054.2.camel@xyzzy>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox