CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Smmit
Smmit

Reputation: 31

How to select parent item and child with highest count using SQL

[![enter image description here][1]][1]

So this is my database and this is my code for now

select distinct continentname, countryname, max(numberoflanguages)
from continent
inner join country on continent.continent = country.continent
group by continentname, countryname
order by continentname

It still shows every single continent but I need the top 1 country in every single continent who have the most numberoflanguages.

Kind of like this as output

Please correct me.

Upvotes: 0

Views: 165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use window functions:

select cc.*
from (select c.continentname, co.countryname, co.numberoflanguages,
             row_number() over (partition by c.continentname order by co.numberoflanguages desc) as seqnum
      from continent c join
           country co
           on c.continent = co.continent
     ) cc
where seqnum = 1;

Upvotes: 1

Related Questions