Query Improvement??

From: Bret Stern <bret_stern(at)machinemanagement(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query Improvement??
Date: 2017-11-05 21:39:34
Message-ID: 1509917974.3996.24.camel@bret.machinemanagement.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Goal is to return all vendors which exist in all three companies

I think I got lucky figuring this out. Is there an obviously better way?

combined_item_master looks like this:
company_code character varying(10) NOT NULL,
primary_vendor_no character varying(7)
..more fields

data looks like this:

company_code | primary_vendor
AAA 003
BBB 004
CCC 001
CCC 004
AAA 123
BBB 123
CCC 123
BBB 003

Query returns all primary_vendor_no (as vendor_locations) which exist in
all three companies
results:
vendor_locations
123

Here's the query

select primary_vendor_no, count(primary_vendor_no) as vendor_locations
from
(
SELECT distinct primary_vendor_no, company_code
FROM combined_item_master
group by primary_vendor_no, company_code
) as a

group by primary_vendor_no
having count(primary_vendor_no)=3
order by vendor_locations DESC, primary_vendor_no

Thanks
Bret

Browse pgsql-general by date

  From Date Subject
Next Message tao tony 2017-11-06 01:11:35 Re: checkpoint and recovering process use too much memory
Previous Message Mark Fletcher 2017-11-05 16:11:36 Re: pg_logical/snapshots directory