sql - How to select max of count in PostgreSQL -


i have table in postgresql following schema:

category    |   type  ------------+---------           |   0  c           |   11  b           |   5  d           |   1  d           |   0  f           |   2    e           |   11  e           |   9  .           |   .  .           |   . 

how can select category wise maximum occurrence of type? following give me all:

select      category,      type,      count(*)      table group      category,      type order      category,      count      desc 

my expected result this:

cat |type |count
--------+-------+------

a |0 |5

b |5 |30

c |2 |20

d |3 |10

that type max occurrence in each category count of type.

if understand correctly, can use window functions:

select category, type, cnt (select category, type, count(*) cnt,              row_number() on (partition type order count(*) desc) seqnum       table       group category, type      ) ct seqnum = 1; 

Comments

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

javascript - IIFE: var vs this - is there any difference? -

r - Grow a ffdf data frame on disk gradually -