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

Re: join with 1 row limit

From: Frank Bax <fbax(at)sympatico(dot)ca>
To:
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: join with 1 row limit
Date: 2011-01-15 13:19:25
Message-ID: 4D319EDD.1040902@sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-novice
I posted basically the same question last year and did not get a good 
answer.

I expect that in the original query below; there are multiple images per 
product in product_images and for this query the OP wants only one of 
those images per product in the final result.  OP still wants all 
products in the final query.

I will use this query to rephrase my question of last year.

How would we modify this JOIN clause to return the smallest (or largest) 
image for each product?  In case of tie (multiple images with same size) 
we are once again back to the OP's question - how to return only one image?

Frank




On 01/15/11 06:45, Vibhor Kumar wrote:
> Try:
>
> SELECT * FROM products p LEFT JOIN products_images pi ON p.id = pi.product_id WHERE products.company = 7 LIMIT 1;
>
> On Jan 15, 2011, at 8:02 AM, Behringer Behringer wrote:
>
>> SELECT *
>> FROM
>> products p LEFT JOIN
>> products_images pi ON p.id = pi.product_id  LIMIT 1
>> WHERE products.company = 7
>
> Thanks&  Regards,
> Vibhor Kumar
>
>
>
>
>
>
>
>
>
>
>


In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2011-01-15 15:49:09
Subject: Re: join with 1 row limit
Previous:From: YAMAMOTO TakashiDate: 2011-01-15 13:17:53
Subject: Re: async fast-path calls?

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