From: Josh Berkus <josh@agliodbs.com>
To: Markus Bertheau <twanger@bluetwanger.de>
To: postgres sql list <pgsql-sql@postgresql.org>
Subject: Re: why can a named subselect not be used in a where
Date: Thu, 24 Jan 2002 09:58:56 -0800
Message-ID: <web-675145@davinci.ethosmedia.com> (raw)
In-Reply-To: <1011894031.29186.24.camel@entwicklung01.cenes.de>
Markus,
> 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?
There's two problems with the above query that have nothing to do with
named subselects, which are well-supported by PostgreSQL (in fact, as
of 7.1.3, Postgres has better support for subselects of all sorts than
any other major RDBMS platform).
1. "Max" is a reserved word in SQL. You are confusing the parser.
Pick another name.
2. While you are already aware that the example query is not the most
efficient construction, I also think you are doing your aliasing in
the wrong place:
select personen.*, (select max(last_visit) as max_visit from sessions
where sessions.personen_id = personen.personen_id) from
personen
... but I could be wrong, as I almost never find any reason to use a
sub-select in the SELECT line.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
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: josh@agliodbs.com, twanger@bluetwanger.de
Subject: Re: why can a named subselect not be used in a where
In-Reply-To: <web-675145@davinci.ethosmedia.com>
* 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