pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: Chris Bitmead <chris.bitmead@bigfoot.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Finding the "most recent" rows
Date: Thu, 29 Apr 1999 13:04:54 +0300
Message-ID: <l03130300b34ddda65755@[147.233.159.109]> (raw)
In-Reply-To: <371FF985.FA06E59B@bigfoot.com>
References: <20915.924840503@sss.pgh.pa.us>

At 07:39 +0300 on 23/04/1999, Chris Bitmead wrote:


> httpd=> select title, summary, time from story t where time IN (select
> max(s.time) from story s GROUP BY s.title);
> ERROR:  parser: Subselect has too many or too few fields.

Of course it does. Since you group by a field which is not in the select
list, Postgres adds it silently to the fields to be selected. Thus the
subselect has two fields in each row, not just a single time field.

I think the proper syntax by the standard would be something like:

 SELECT title, summary, time
 FROM story t
 WHERE (time, title) IN (
    SELECT s.title, max( s.time )
    FROM story s
    GROUP BY s.title
 );

But I'm not sure Postgres even supports this format (of comparing against
several fields).

In any case, the best would be to select just one tuple in the subselect
and have it return only the time, by constraining the title. But
syntactically, the above is what you were trying to do.

Anyway, Postgres adds the group field to the query, which the standard
requires and common practice doesn't. I think perhaps after doing the
grouping, Postgres should drop that field, since it wasn't originally
requested.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (24+ messages)  latest in thread

Message-ID: <l03130300b34ddda65755@[147.233.159.109]>
Permalink:  ../l03130300b34ddda65755@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130300b34ddda65755@[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, chris.bitmead@bigfoot.com
  Subject: Re: [SQL] Finding the "most recent" rows
  In-Reply-To: <l03130300b34ddda65755@[147.233.159.109]>
  References: <20915.924840503@sss.pgh.pa.us> <l03130300b34ddda65755@[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