[GENERAL] Query Improvement??
От | Bret Stern |
---|---|
Тема | [GENERAL] Query Improvement?? |
Дата | |
Msg-id | 1509917974.3996.24.camel@bret.machinemanagement.com обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: