Re: difficult query (for me)

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(at)xyzzy(dot)dhs(dot)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