Received: from leela.one-2-one.net (leela.one-2-one.net [217.115.142.65]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0OHeYl06263 for ; Thu, 24 Jan 2002 12:40:34 -0500 (EST) (envelope-from twanger@bluetwanger.de) Received: from [192.168.0.14] (p3E9EC076.dip.t-dialin.net [62.158.192.118]) by leela.one-2-one.net (8.11.0/8.11.0) with ESMTP id g0OHfXD18381 for ; Thu, 24 Jan 2002 18:41:33 +0100 Subject: why can a named subselect not be used in a where condition? From: Markus Bertheau To: postgres sql list Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-XVnckY2KRKmlynDv7BpS" X-Mailer: Evolution/1.0.1 Date: 24 Jan 2002 18:40:30 +0100 Message-Id: <1011894031.29186.24.camel@entwicklung01.cenes.de> Mime-Version: 1.0 X-Archive-Number: 200201/271 X-Sequence-Number: 6355 --=-XVnckY2KRKmlynDv7BpS Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hello, cenes_test=3D> select version(); version=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20 ------------------------------------------------------------- PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) cenes_test=3D> select personen.*, (select max(last_visit) from sessions where sessions.personen_id =3D personen.personen_id) as max from personen where max between 1009148400 and 1011913200 order by max desc; ERROR: Attribute 'max' not found I again think that the table structure is not neccesary to understand my question. Well, why can't I use max in the where clause? I do know, thanks to Josh Berkus, that this query should be reformed using sth like select personen.f1, personen.f2, max(last_visit) from personen join sessions on sessions.personen_id =3D personen.personen_id group by personen.f1, personen.f2 (I know that this query doesn't include persons that don't have a row with their personen_id in sessions) Related: cenes=3D> select version(); version=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 --------------------------------------------------------------- PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 (1 row) cenes=3D> select personen.*, (select max(last_visit) from sessions where sessions.personen_id =3D personen.personen_id) from personen where (select max(zeitstempel) from sessions where sessions.personen_id =3D personen.personen_id) between 1009148400 and 1011913200; ERROR: ExecEvalExpr: unknown expression type 501 That is a query we have tried on 7.0.2 because of curiosity. But the error message looks weird. What does it mean? Thank you for you information. Markus Bertheau --=-XVnckY2KRKmlynDv7BpS Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQA8UEcOKNEjAb345r4RAl0QAJ9Zic1aBUZgaA82smKGpt6h4VmHiwCeJy6Z 2xepWT/J3CNwiEDlseYKUQs= =8fsh -----END PGP SIGNATURE----- --=-XVnckY2KRKmlynDv7BpS--