From: | Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il> |
---|---|
To: | Chad Miller <cmiller+ps(at)surfsouth(dot)com>, pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] err: select f() from i where (f()) in (select f() from x group by j); |
Date: | 1999-08-15 13:40:36 |
Message-ID: | l03130308b3dc71c61b61@[147.233.159.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
At 03:13 +0300 on 14/08/1999, Chad Miller wrote:
> If I create a view, with
>
> > create view foo as select timestamp, sum(num) from timelines where
>(name='foo' or name='bar' or name='baz') group by timestamp;
>
> I get:
> Table = foo
> +-----------------------------+----------------------------------+-------+
> | Field | Type | Length|
> +-----------------------------+----------------------------------+-------+
> | timestamp | datetime | 8 |
> | sum | int2 | 2 |
> +-----------------------------+----------------------------------+-------+
>
> (Note the fields' naming) Still -- I'd like a more elegant way to do this,
> than creating a view.
>
> I came up with:
>
> > select timestamp, max(sum) from timelines where (timestamp, sum) in
>(select timestamp, sum(num) from timelines where (name='foo' or name='bar'
>or name='baz') group by timestamp);
>
> ...which returns
>
> < ERROR: attribute 'sum' not found
The way to get rid of names that would make your life hard is to put field
aliases in the CREATE VIEW:
create view foo as select timestamp as ts_col, sum(num) as sum_col
from timelines, where ....;
Anyway, it's not the problem here. The problem is that you selected from
timelines instead of from foo.
As for a more elegant way of doing the same task, I'm not entirely sure,
because I don't have the latest PostgreSQL here, but here is a general idea:
SELECT timestamp, sum( num ) as the_sum
FROM timelines
WHERE (name='foo' or name='bar' or name='baz')
GROUP BY timestamp
ORDER BY the_sum DESC
LIMIT 1;
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
From | Date | Subject | |
---|---|---|---|
Next Message | Len Morgan | 1999-08-15 14:01:36 | Agregate Problem? |
Previous Message | Herouth Maoz | 1999-08-15 13:26:31 | Re: [SQL] Multiple values for a field |