Re: Join issue on a maximum value

On Wed, Apr 21, 2004 at 14:29:34 -0400,
Heflin <hhogan ( at ) tampabay ( dot ) rr ( dot ) com> wrote:
>
> 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';
>

In my opinion there are 2 problem: how can you make the query and how
many rows is in the result (performace)? Usually when you have more rows
in the result you can use the LIMIT and OFFSET. So you can reach the
result to unfold more pages. So I bult in these LIMIT and OFFSET into
the queries.

2 new possibilities:

SELECT IDSEL.*, (SELECT image_descr FROM image WHERE
IDSEL.image_id=image.image_id) FROM (
SELECT auction.auction_id, max(image.image_id) AS image_id FROM
auction JOIN image USING (auction_id)
WHERE auction_owner = 'Mabel'
GROUP BY auction.auction_id
ORDER BY auction.auction_id
LIMIT 10 OFFSET 0
) AS IDSEL;

or

SELECT DISTINCT ON (image.auction_id) image.auction_id,
image.image_id, image.image_descr FROM auction
JOIN image USING (auction_id)
WHERE auction.auction_owner = 'Mabel'
ORDER BY image.auction_id, (-image.image_id)
LIMIT 10 OFFSET 0;

Index suggestions:
CREATE INDEX auction_auction_owner on auction(auction_owner);
CREATE INDEX image_auction_id on image(auction_id);
CREATE INDEX image_auction_id_neg_image_id on image(auction_id,
(-image_id)); -- Specially for the second solution

The second solution build on Bruno Wolff III's ideas:
http://archives.postgresql.org/pgsql-sql/2004-04/msg00211.php and
http://archives.postgresql.org/pgsql-sql/2004-04/msg00262.php .

You can see more solutions for your problem. You have to select the
best performance solution for your specific databse. Use the EXPLAIN!

Regards, Antal Attila