On 7/11/06,
Bryce Nesbitt <
bryce1@obviously.com> wrote:
Tom Lane wrote:
> But as far as the underlying misconception goes, you seem to think that
> "4" in the WHERE clause might somehow be taken as referring to the
> fourth SELECT result column (why you don't think that the "1" would
> likewise refer to the first result column isn't clear). This is not so.
> "4" means the numeric value four. There is a special case in ORDER BY
> and GROUP BY that an argument consisting of a simple integer literal
> constant will be taken as a reference to an output column. This is an
> ugly kluge IMHO...
Yeah, it was a longshot. I only tried it because the column label did
NOT work, and I had some gut reaction to repeating the same function twice:
As I mentioned before: the only difference between HAVING and WHERE is that WHERE occurs before a GROUP BY and HAVING occurs after.
stage=# SELECT pod_code, lat, lon,
calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod
WHERE dist < 1 ORDER BY dist desc limit 10;
ERROR: column "dist" does not exist
You cannot use an alias from the select column list in your WHERE clause because the where is the criteria done BEFORE your columns are pulled out. This is especially noticable when doing a GROUP BY since the WHERE is done before the GROUP BY and the returned column values are gathered after the GROUP BY. If you want to use an alias, do a subquery and then put your where in the outer query.
I believe the ORDER BY is done last but that may be dependent on the database implementation. It does make sense to think of ORDER BY to be done last though. For that reason it can use the alias.
stage=# SELECT pod_code, lat, lon,
calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod
WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1 ORDER BY
dist desc limit 5;
pod_code | lat | lon | dist
----------+-----------+-------------+-------------------
5 | 37.792022 | -122.404247 | 0.988808031847045
62 | 37.780166 | -122.409615 | 0.944907273102541
4 | 37.798528 | -122.409582 | 0.919592583879426
86 | 37.777529 | -122.417982 | 0.866416010967029
68 | 37.789915 | -122.406926 | 0.82867104307647
(5 rows)
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com==================================================================