Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select distinct question ... complicated

Select distinct question ... complicated

From
The Pennant Shop
Date:
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

Re: Select distinct question ... complicated

From
Bruno Wolff III
Date:
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;

Re: Select distinct question ... complicated

From
"John Liu"
Date:
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

Re: Select distinct question ... complicated

From
Darcy Buskermolen
Date:
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