How to get value from a column what has maximum count in postgres?

Question

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.

forpas · Accepted Answer · 2020-07-06 10:54:04Z

4

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  |

answered Jul 6, 2020 at 10:54

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2020-07-06 11:06:55Z

4

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;

edited Jul 6, 2020 at 11:06

answered Jul 6, 2020 at 10:55

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

1 Comment

forpas Over a year ago

No need for the subquery: db-fiddle.com/f/peoPUMUhR6PhrH4TBR8TMv/1

Collectives™ on Stack Overflow

How to get value from a column what has maximum count in postgres?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related