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

Wed, 6 Aug 2003 12:05:47 -0700 (PDT)
The Pennant Shop <vicmogroup@yahoo.com>
Select distinct question ... complicated
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

Wed, 6 Aug 2003 14:11:18 -0500
"John Liu" <johnl@emrx.com>
Re: Select distinct question ... complicated
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

Wed, 6 Aug 2003 12:21:10 -0700
Darcy Buskermolen <darcy@wavefire.com>
Re: Select distinct question ... complicated
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;

Wed, 6 Aug 2003 14:25:48 -0500
Bruno Wolff III <bruno@wolff.to>
Re: Select distinct question ... complicated