pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWHERE on an alias
9+ messages / 5 participants
[nested] [flat]
* WHERE on an alias
@ 2001-08-24 18:11 Joseph Shraibman <jks@selectacast.net>
2001-08-24 19:27 ` Re: WHERE on an alias Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-24 19:58 ` Re: WHERE on an alias Josh Berkus <josh@agliodbs.com>
2001-08-24 20:56 ` Re: WHERE on an alias Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 9+ messages in thread
From: Joseph Shraibman @ 2001-08-24 18:11 UTC (permalink / raw)
To: pgsql-sql
playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
ERROR: Attribute 'dsum' not found
Why can we GROUP BY on an alias but not do a WHERE on an alias? I have a subselect that
explain shows is being run twice if I have to put it in the WHERE clause.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
@ 2001-08-24 19:27 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-24 19:31 ` Re: WHERE on an alias Joseph Shraibman <jks@selectacast.net>
2 siblings, 1 reply; 9+ messages in thread
From: Stephan Szabo @ 2001-08-24 19:27 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
On Fri, 24 Aug 2001, Joseph Shraibman wrote:
> playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
> ERROR: Attribute 'dsum' not found
>
> Why can we GROUP BY on an alias but not do a WHERE on an alias? I have a subselect that
> explain shows is being run twice if I have to put it in the WHERE clause.
Somewhat unrelated question, are you sure you want where and not having?
IIRC, where is going to select rows before things like the group by
occur, and that doesn't seem to be what you'd want here, right?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
2001-08-24 19:27 ` Re: WHERE on an alias Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-08-24 19:31 ` Joseph Shraibman <jks@selectacast.net>
2001-08-24 20:52 ` Re: WHERE on an alias Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 9+ messages in thread
From: Joseph Shraibman @ 2001-08-24 19:31 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
That was just an example. My code doesn't use group by, it uses a subselect. And I
checked and having doesn't work with aliases either.
Stephan Szabo wrote:
> On Fri, 24 Aug 2001, Joseph Shraibman wrote:
>
>
>>playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
>>ERROR: Attribute 'dsum' not found
>>
>>Why can we GROUP BY on an alias but not do a WHERE on an alias? I have a subselect that
>>explain shows is being run twice if I have to put it in the WHERE clause.
>>
>
> Somewhat unrelated question, are you sure you want where and not having?
> IIRC, where is going to select rows before things like the group by
> occur, and that doesn't seem to be what you'd want here, right?
>
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
2001-08-24 19:27 ` Re: WHERE on an alias Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-24 19:31 ` Re: WHERE on an alias Joseph Shraibman <jks@selectacast.net>
@ 2001-08-24 20:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2001-08-24 20:52 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
On Fri, 24 Aug 2001, Joseph Shraibman wrote:
> That was just an example. My code doesn't use group by, it uses a subselect. And I
> checked and having doesn't work with aliases either.
Okay. I know why WHERE doesn't support aliases, but was a little confused
by the example. The reason for that is that the select list isn't
worked out until after the rows are qualified and it technically fits
spec.
I think it might fall into something like (untested):
select * from (select a, (...) as subsel from taba) as foo
where foo.subsel>5;
to fit standard, but that's pretty ugly...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
@ 2001-08-24 19:58 ` Josh Berkus <josh@agliodbs.com>
2001-08-24 20:18 ` Re: WHERE on an alias Joseph Shraibman <jks@selectacast.net>
2 siblings, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2001-08-24 19:58 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
Joseph,
> playpen=# select a, sum(b) as dsum from taba where dsum > 5 group
> by(a);
> ERROR: Attribute 'dsum' not found
>
> Why can we GROUP BY on an alias but not do a WHERE on an alias? I
> have a subselect that
> explain shows is being run twice if I have to put it in the WHERE
> clause.
Ah, but you are not trying to do a WHERE on an alias ... you are trying
to do a WHERE on an *aggregate*, which is a different thing. Consult
your favorite SQL manual; the construction you want is:
SELECT a, sum(b) as dsum
FROM taba
GROUP BY a
HAVING dsum > 5;
-Josh Berkus
______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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
2001-08-24 19:58 ` Re: WHERE on an alias Josh Berkus <josh@agliodbs.com>
@ 2001-08-24 20:18 ` Joseph Shraibman <jks@selectacast.net>
2001-08-24 20:43 ` Re: WHERE on an alias Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 9+ messages in thread
From: Joseph Shraibman @ 2001-08-24 20:18 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
playpen=# SELECT a, sum(b) as dsum
playpen-# FROM taba
playpen-# GROUP BY a
playpen-# HAVING dsum > 5;
ERROR: Attribute 'dsum' not found
HAVING does not work either. In fact this example is in the pg docs for SELECT:
SELECT kind, SUM(len) AS total
FROM films
GROUP BY kind
HAVING SUM(len) < INTERVAL '5 hour';
Josh Berkus wrote:
> Joseph,
>
>
>>playpen=# select a, sum(b) as dsum from taba where dsum > 5 group
>>by(a);
>>ERROR: Attribute 'dsum' not found
>>
>>Why can we GROUP BY on an alias but not do a WHERE on an alias? I
>>have a subselect that
>>explain shows is being run twice if I have to put it in the WHERE
>>clause.
>>
>
> Ah, but you are not trying to do a WHERE on an alias ... you are trying
> to do a WHERE on an *aggregate*, which is a different thing. Consult
> your favorite SQL manual; the construction you want is:
>
> SELECT a, sum(b) as dsum
> FROM taba
> GROUP BY a
> HAVING dsum > 5;
>
>
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
2001-08-24 19:58 ` Re: WHERE on an alias Josh Berkus <josh@agliodbs.com>
2001-08-24 20:18 ` Re: WHERE on an alias Joseph Shraibman <jks@selectacast.net>
@ 2001-08-24 20:43 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Josh Berkus @ 2001-08-24 20:43 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
Joseph,
> playpen=# SELECT a, sum(b) as dsum
> playpen-# FROM taba
> playpen-# GROUP BY a
> playpen-# HAVING dsum > 5;
Sorry, my mistake:
SELECT a, sum(b) as dsum
FROM taba
GROUP BY a
HAVING sum(b) > 5;
I had the impression that aliases for aggregates were allowed in
Postgres HAVING clauses. Apparently not.
-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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
@ 2001-08-24 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2001-08-24 20:56 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
Joseph Shraibman <jks@selectacast.net> writes:
> playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
> ERROR: Attribute 'dsum' not found
> Why can we GROUP BY on an alias but not do a WHERE on an alias?
Because WHERE is computed before the select's output list is.
Strictly speaking you shouldn't be able to GROUP on an alias either (the
SQL spec doesn't allow it). We accept that for historical reasons only,
ie, our interpretation of GROUP used to be wrong and we didn't want to
break applications that relied on the wrong interpretation.
Note that writing a GROUP on an alias does *not* mean the alias is only
computed once. It saves no computation, only writing out the expression
twice.
> I have a subselect that
> explain shows is being run twice if I have to put it in the WHERE clause.
Possibly you could restructure your query into something with a
subselect in the FROM clause?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: WHERE on an alias
@ 2001-08-29 06:27 Andre Schnabel <A_Schnabel@t-online.de>
0 siblings, 0 replies; 9+ messages in thread
From: A_Schnabel@t-online.de @ 2001-08-29 06:27 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; pgsql-sql
----- Original Message -----
From: "Joseph Shraibman" <jks@selectacast.net>
Subject: Re: [SQL] WHERE on an alias
> If I try to put a distinct on in my subselect int the from I get:
>
> ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressions
>
> what does that mean?
You need to (at least) ORDER BY the expression you want to have the
DISTINCT ON.
Tom's example would work fine:
> > select distinct on (ml.f1,ml.f2) * from ut,ml
> > where ut.f1 = ml.f1 and ut.f2 = ml.f2
> > order by ml.f1, ml.f2, ml.f3 desc;
Wrong:
select distinct on (ml.f1,ml.f2) * from ut,ml
where ut.f1 = ml.f1 and ut.f2 = ml.f2
order by ml.f2, ml.f1, ml.f3 desc;
Andre
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2001-08-29 06:27 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-24 18:11 WHERE on an alias Joseph Shraibman <jks@selectacast.net>
2001-08-24 19:27 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-24 19:31 ` Joseph Shraibman <jks@selectacast.net>
2001-08-24 20:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-08-24 19:58 ` Josh Berkus <josh@agliodbs.com>
2001-08-24 20:18 ` Joseph Shraibman <jks@selectacast.net>
2001-08-24 20:43 ` Josh Berkus <josh@agliodbs.com>
2001-08-24 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-29 06:27 Re: WHERE on an alias Andre Schnabel <A_Schnabel@t-online.de>
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