Subject: Re: difficult query (for me)
From: "Andrew G. Hammond" <drew@xyzzy.dhs.org>
To: QdlatY <qdlaty@wielun.dhs.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1132751000.20011229162035@wielun.dhs.org>
References: <1132751000.20011229162035@wielun.dhs.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-pGXRIRRsfhxIGl/QBzGo"
Date: 29 Dec 2001 11:05:40 -0500
Message-Id: <1009641941.1054.2.camel@xyzzy>
Mime-Version: 1.0

--=-pGXRIRRsfhxIGl/QBzGo
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Sat, 2001-12-29 at 10:20, QdlatY wrote:
> Hello
>=20
> I will try to explain my problem as clearly as i can.
>=20
> So, I have tree tables
> Named:
>=20
> Tables
> Threads
> Posts
>=20
> 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=
=20
> table)
> Posts has attribute ThreadId and CreateTime
>=20
> (i only wrote important columns to this query)
>=20
> Now, I have Tables row with ID =3D 10
> And then i want to have list of all Threads (belongs to Table with id =3D=
 10,=20
> so WHERE Threads.TableId =3D 10) sorted by CreateTime of newest Posts row=
=20
> 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 =3D 10 AND t.id =3D 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 =3D 10
>=20
> Thread
> ID TableID
> 1  10
> 2  10
> 3  11
>=20
> Posts
> ThreadId  CreateTime (i will use nubers only to explain)
> 1         10
> 1         20
> 1         25
> 2         43
> 3         02
>=20
>=20
> So i want to have result from Table ID =3D 10:
> Threads
> ID
> 2    (because  CreateTime of Posts from 2 Thread is highest)
> 1    (and so on...)
>=20
> (Thread 3 not belongs to Table.ID =3D 10)
>=20
> Of course result can contains adequate Posts columns but don't must to...
>=20
> I hope you will able to help me, i think it's not difficult but i'm a=20
> little newbie, so, i gave up on this :PPP

Hope this helps.

--=20
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org=20=20
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20
613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

--=-pGXRIRRsfhxIGl/QBzGo
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iQCVAwUAPC3p1MaBTSA3ULb1AQLFjgP9GzPT2cDa3eVobOOl+MuM/J/ADNHAMnyL
FYXh4TOu4gXJEr2kDLY1ypvql9m4Nt9a30bGNBHKxvQ8k5ZycE37mJjbw+KO76fD
PO4pWmFb15RE3rO3xEwEQlSXUww2H8qmlHITiBRo2MDrB0cNaE7A3r/ZtGH763Kb
WL/Hytrogz4=
=tMLk
-----END PGP SIGNATURE-----

--=-pGXRIRRsfhxIGl/QBzGo--