Skip site navigation (1) Skip section navigation (2)

Re: Join issue on a maximum value

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Heflin <hhogan(at)tampabay(dot)rr(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Join issue on a maximum value
Date: 2004-04-21 18:51:22
Message-ID: 20040421185122.GA31195@wolff.to (view raw or flat)
Thread:
Lists: pgsql-sql
On Wed, Apr 21, 2004 at 14:29:34 -0400,
  Heflin <hhogan(at)tampabay(dot)rr(dot)com> wrote:
> 
> So a basic JOIN gets this:
> 
> SELECT auction.auction_id, image.image_id, image.image_descr
> FROM auction JOIN image ON auction.auction_id = image.auction_id
> WHERE auction.auction_owner = 'Mabel';
> 
> Now the problem: I can't seem to remember how to get only the max value 
> for the image_id for each auction_id so that the result set would be:

The postgres specific way of doing this is:
SELECT DISTINCT ON (auction.auction_id)
    auction.auction_id, image.image_id, image.image_descr
  FROM auction JOIN image ON auction.auction_id = image.auction_id
  WHERE auction.auction_owner = 'Mabel'
  ORDER BY auction.auction_id, image.image_id DESC
;

The more standard way to do it would be joining auction and image
with a group by and max to get the highest image_id and then joining
that result to image again to get the corresponding description.

In response to

Responses

pgsql-sql by date

Next:From: Jeremy SemeiksDate: 2004-04-21 19:12:48
Subject: Re: Join issue on a maximum value
Previous:From: HeflinDate: 2004-04-21 18:29:34
Subject: Join issue on a maximum value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group