grouping records by a field and submit a query on each group in sql

Question

I have a table like this:

create table product_company (
    id int PRIMARY KEY,
    productName varchar(100),
    companyName varchar(100),
    price int
);

I want to know the name of the product which it has the second rank in price in each company.

for example if company1 has three product product1=30, product2=50 and product3=15(the assignment shows the price of each product in this company) so product1 has the second rank in price property in company1 and I want to write a query that returns something like below:

company1 product1
company2 ...
...

I mean for every company I want to know the product that has the second rank in price within that company. I don't know how to use group by clause because group by is working fine by aggregate functions but I don't want the maximum in price.

I want to write this query with standard sql queries and clauses and without some special funcions that may not work in some DBMS

Answer 1

If you are running MySQL 8.0, you can use window function dense_rank():

select *
from (
    select
        pc.*,
        dense_rank() over(partition by companyName order by price desc) rn
    from product_company pc
) t
where rn = 2

In earlier versions, one solution is to filter with a correlated subquery. But you have to be careful to properly handle possible top ties. This should do it:

select pc.*
from product_company pc
where (
    select count(distinct pc1.price) 
    from product_company pc1 
    where pc1.companyName = pc.companyName and pc1.price > pc.price
) = 1