Re: why can a named subselect not be used in a where - Mailing list pgsql-sql

From Josh Berkus
Subject Re: why can a named subselect not be used in a where
Date
Msg-id web-675145@davinci.ethosmedia.com
Whole thread Raw
In response to why can a named subselect not be used in a where condition?  (Markus Bertheau <twanger@bluetwanger.de>)
List pgsql-sql
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 withnamed subselects, which are well-supported by
PostgreSQL(in fact, asof 7.1.3, Postgres has better support for subselects of all sorts thanany 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 mostefficient construction, I also think you are doing
youraliasing inthe wrong place:
 
select personen.*, (select max(last_visit) as max_visit from sessionswhere sessions.personen_id = personen.personen_id)
frompersonen
 
... but I could be wrong, as I almost never find any reason to use asub-select in the SELECT line.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 


pgsql-sql by date:

Previous
From: Markus Bertheau
Date:
Subject: auto group by
Next
From: Tom Lane
Date:
Subject: Re: why can a named subselect not be used in a where condition?