How to get maximum value from two tables where only IDs overlapping?

Question

I have two different tables with headers (shortened)

table a

supplier_id, company_name

table b

product_id, product_name, supplier_id, unit_price

I have to produce a table with only maximum unit price products of each company. The header of the resulting table is: company_name, product_name, unit_ price.

My code is actually selecting each product.

SELECT suppliers.company_name AS company, products.product_name AS product, MAX(products.unit_price) AS price FROM suppliers
JOIN products
    ON products.supplier_id = suppliers.supplier_id
GROUP BY company_name, product_name, unit_price
ORDER BY price DESC;

Company     Product     Max unit price
company a   product x      value 1
company b   product y      value 2

S-Man · Accepted Answer · 2019-08-08T11:44:55.220

0

demo:db<>fiddle

It seems that you are searching for DISTINCT ON. This gives out the first record of an ordered group. In your case the groups are the company_name and the order within them is the price (in descending order to get the highest value first):

SELECT DISTINCT ON (suppliers.company_name) 
    suppliers.company_name AS company, 
    products.product_name AS product, 
    products.unit_price AS price 
FROM suppliers
JOIN products
    ON products.supplier_id = suppliers.supplier_id
ORDER BY suppliers.company_name, products.unit_price DESC;

edited Aug 08 '19 at 11:44

answered Aug 08 '19 at 10:03

S-Man

22,521
7
40
63

Thanks a lot. It's perfect. – turo62 Aug 08 '19 at 10:07
Added demo fiddle – S-Man Aug 08 '19 at 10:10
I am sorry but just realised that with your code the order by is according to company but (not mentioned in my question) order should be on price primarily in descending order. – turo62 Aug 08 '19 at 11:35
@turo62 but if you want the highest price per company you have to order the group of companies first. This order gives you all products per company together and then orders the products per company by price. Tell us what exactly is wrong with the result? Maybe you should add some sample code and expected output. – S-Man Aug 08 '19 at 11:47
As Gordon stated correctly: The GROUP BY was not necessary. I just copied the query from the question and missed to remove the GROUP BY line. Fixed it. – S-Man Aug 08 '19 at 11:50

score 0 · Answer 2 · answered Aug 08 '19 at 10:58

You can use distinct on, but you don't need group by:

SELECT DISTINCT ON (s.company_name) s.company_name AS company, p.product_name AS product, p.unit_price
FROM suppliers s JOIN
     products p
     ON p.supplier_id = s.supplier_id
ORDER BY s.company_name, unit_price DESC;

You can do this with aggregation, but you need a bit of a trick to get the product. Here is a way using arrays:

SELECT s.company_name AS company, 
       (ARRAY_AGG(p.product_name ORDER BY p.unit_price DESC))[1] AS product, 
      MAX(p.unit_price) as max_price
FROM suppliers s JOIN
     products p
     ON p.supplier_id = s.supplier_id
GROUP BY s.company_name;

I would expect the DISTINCT ON (without GROUP BY) to have the better performance.

How to get maximum value from two tables where only IDs overlapping?

2 Answers2