I understand the MAX function returns a maximum value from a given column for a numeric column. I want to return the maximum count of a column. The below table has two columns maker, product.
maker product
A Printer
B Laptop
B Laptop
A Printer
A Monitor
A Printer
A Scanner
But when I run the query '''select maker, max(type) from table group by maker; ''' I am getting the result as
maker product
A Monitor
B Laptop
I want which product has a maximum count with respect to the maker like below.
maker product
A Printer
B Laptop
Note: As maker count differs, we can't set a common number in Having clause.
You can do it with RANK() window function:
select t.maker, t.product
from (
select maker, product,
rank() over (partition by maker order by count(*) desc) rn
from tablename
group by maker, product
) t
where t.rn = 1
See the demo.
Results:
| maker | product |
| ----- | ------- |
| A | Printer |
| B | Laptop |
In PostgreSQL, the simplest solution is to use DISTINCT ON:
SELECT DISTINCT ON(maker)
maker, product
FROM atable
GROUP BY maker, product
ORDER BY maker, count(*) DESC;