CODEWITHSUNDEEP
sqldatabasepostgresqlgroup-bymax
sepisoad
sepisoad

Reputation: 2281

how to get top count of a column per group in sql (postgresql)

lets say I have a table like this:

company investor
apple john
apple john
apple john
apple alex
google max
google max
google oliver

I want to write a query that finds the top investor per company and get a response like this:

company investor count
apple john 3
google max 2

obviously my example is stupid, but in terms of data and expected result it fully represents my concerns.

UPDATED:

as mentioned by @jarlh if a company has more than one top investor then I would like to get the first one as sorted by name, like if we have one more row for google and oliver then I would want to get max and 2 because of the names being sorted alphabetically in result;

Upvotes: 0

Views: 881

Answers (2)

Abishek VK
Abishek VK

Reputation: 524

Please use the below query,

SELECT 
COMPANY
,INVESTOR
,COUNTS
FROM
(
SELECT 
COMPANY
,INVESTOR
,COUNT(*) COUNTS
,DENSE_RANK() OVER (PARTITION BY COMPANY ORDER BY COUNTS DESC) RN
FROM TABLE
GROUP BY COMPANY,INVESTOR

)B
WHERE RN=1

this will return all the investor names if there is a tie. If you need only one you can use rank() instead of dense_rank() and add additional fields in order by clause

Upvotes: 0

Philippe
Philippe

Reputation: 1827

You can do this

with x as (
select rank() over (partition by company order by count(*) desc, investor) as rank, company,investor,count(*) as qty from test group by company,investor)
select * from x where rank = 1

in case of equality between 2 investors and you want to get both then run the query like this

with x as (
select rank() over (partition by company order by count(*) desc) as rank, company,investor,count(*) as qty from test group by company,investor)
select * from x where rank = 1

Result here

Upvotes: 1

Related Questions