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
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 |