Обсуждение: Select distinct question ... complicated

Hi ,

I have a table:
item location
aaa   10
aaa   20
bbb   10
bbb   10
ccc   10
ccc   20

I need to select distinct items where locations are
the same. So result set should look like:
item loation
bbb 10
Already spent 7 hours on this one.

Thanks a lot / Alex



__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

On Wed, Aug 06, 2003 at 12:05:47 -0700, The Pennant Shop <vicmogroup@yahoo.com> wrote:
> Hi ,
> 
> I have a table:
> item location
> aaa   10
> aaa   20
> bbb   10
> bbb   10
> ccc   10
> ccc   20
> 
> I need to select distinct items where locations are
> the same. So result set should look like:
> item loation
> bbb 10
> Already spent 7 hours on this one.

select item, location from table group by item, location having count(*) > 1;

It's easier to create a simple pgsql function 
to do this ...

johnl

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of The Pennant Shop
> Sent: Wednesday, August 06, 2003 2:06 PM
> To: pgsql-hackers@postgresql.org
> Subject: [HACKERS] Select distinct question ... complicated
> 
> 
> Hi ,
> 
> I have a table:
> item location
> aaa   10
> aaa   20
> bbb   10
> bbb   10
> ccc   10
> ccc   20
> 
> I need to select distinct items where locations are
> the same. So result set should look like:
> item loation
> bbb 10
> Already spent 7 hours on this one.
> 
> Thanks a lot / Alex
> 
> 
> 
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

This should be on the otyher lists, novice or general for example but here is
your answer anyway.


SELECT item, location FROM foo GROUP BY item,location HAVING count(item) >1
AND count(location) > 1;



On Wednesday 06 August 2003 12:05, The Pennant Shop wrote:
> Hi ,
>
> I have a table:
> item location
> aaa   10
> aaa   20
> bbb   10
> bbb   10
> ccc   10
> ccc   20
>
> I need to select distinct items where locations are
> the same. So result set should look like:
> item loation
> bbb 10
> Already spent 7 hours on this one.
>
> Thanks a lot / Alex
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com