Received: from dagny.surfsouth.com (cmiller@dagny.surfsouth.com [205.139.60.173]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA77050 for ; Fri, 13 Aug 1999 20:13:01 -0400 (EDT) (envelope-from cmiller@dagny.surfsouth.com) Received: (from cmiller@localhost) by dagny.surfsouth.com (8.8.8/8.8.8) id UAA11817; Fri, 13 Aug 1999 20:13:01 -0400 Date: Fri, 13 Aug 1999 20:13:00 -0400 From: Chad Miller To: pgsql-sql@postgresql.org Subject: err: select f() from i where (f()) in (select f() from x group by j); Message-ID: <19990813201300.B11645@surfsouth.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.95.6i X-mom-quote: Finish that packet--children are offline in India! X-lament: There just aren't enough clock-cycles in a day. Hi there. Please forgive my igmorance. I have a table populated with lots of numbers, names, and timestamps. I'd like to find the max of the sums of the numbers and the timestamp. That is, find the timestamp and the sum of the numbers when the sum of the numbers is max. Yow! This does half -- the summing: > select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp; 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 corresponding column in the view was titled ``sum.'' Hmmm. So, perhaps fully writing the ``sum(num)'' is correct. > select timestamp from timelines where (timestamp, sum(num)) in (select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp); ...which returns < ERROR: parser: illegal use of aggregates or non-group column in target list Or... > select timestamp, max(sum(num)) from timelines where (timestamp, sum(num)) in (select timestamp, sum(num) from timelines where (name='foo' or name='bar' or name='baz') group by timestamp); ...which returns < ERROR: type id lookup of 223198728 failed This is making me itch. Advice? Wizardry? Comments? Flames? - chad