pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Chad Miller <cmiller+ps@surfsouth.com>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] err: select f() from i where (f()) in (select f() from x group by j);
Date: Sun, 15 Aug 1999 16:40:36 +0300
Message-ID: <l03130308b3dc71c61b61@[147.233.159.109]> (raw)
In-Reply-To: <19990813201300.B11645@surfsouth.com>

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





view thread (2+ messages)

Message-ID: <l03130308b3dc71c61b61@[147.233.159.109]>
Permalink:  ../l03130308b3dc71c61b61@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130308b3dc71c61b61@[147.233.159.109]

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, cmiller+ps@surfsouth.com
  Subject: Re: [SQL] err: select f() from i where (f()) in (select f() from x group by j);
  In-Reply-To: <l03130308b3dc71c61b61@[147.233.159.109]>
  References: <19990813201300.B11645@surfsouth.com> <l03130308b3dc71c61b61@[147.233.159.109]>

* 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