pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Markus Bertheau <twanger@bluetwanger.de>
To: postgres sql list <pgsql-sql@postgresql.org>
Subject: why can a named subselect not be used in a where condition?
Date: 24 Jan 2002 18:40:30 +0100
Message-ID: <1011894031.29186.24.camel@entwicklung01.cenes.de> (raw)

Hello,

cenes_test=> select version();
                           version                           
-------------------------------------------------------------
 PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

cenes_test=> select personen.*, (select max(last_visit) from sessions
where sessions.personen_id = 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?

<comment>
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 = 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)
</comment>

Related:

cenes=> select version();
                            version                            
---------------------------------------------------------------
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
(1 row)

cenes=> select personen.*, (select max(last_visit) from sessions where
sessions.personen_id = personen.personen_id) from personen where (select
max(zeitstempel) from sessions where sessions.personen_id =
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

Attachments:

  signature.asc (application/pgp-signature, 231B) download

view thread (3+ messages)  latest in thread

Message-ID: <1011894031.29186.24.camel@entwicklung01.cenes.de>
Permalink:  ../1011894031.29186.24.camel@entwicklung01.cenes.de/
Also on:    postgresql.org/message-id/1011894031.29186.24.camel@entwicklung01.cenes.de

 · 

reply

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: twanger@bluetwanger.de
  Subject: Re: why can a named subselect not be used in a where condition?
  In-Reply-To: <1011894031.29186.24.camel@entwicklung01.cenes.de>

* 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