CODEWITHSUNDEEP
mysqlsql
Sendhilkumar Alalasundaram
Sendhilkumar Alalasundaram

Reputation: 1981

How to get the nth highest entry in each group of mysql query

I am a beginner in MySQL and I've stumbled upon this for the past few days now.

Let's say I have a country table as seen below:

code    name            continent       Population
ABW Aruba               North America   103000
AFG Afghanistan         Asia            22720000
AGO Angola              Africa          12878000
AIA Anguilla            North America   8000
ALB Albania             Europe          3401200
AND Andorra             Europe          78000
ARG Argentina           South America   37032000

This table has information of continents and population of each country.

How do I find the country with nth highest population in each continent?

For a specific scenario, How do I find the country with the thrid highest population in each continent?

I checked many SO questions, including this link. But could not find the answer for this problem. Any help would be greatly appreciated!

Upvotes: 2

Views: 1136

Answers (3)

anjali
anjali

Reputation: 84

      with result as 
      (
        select code,name,continent,population,dense_rank() over( order by population ) as rnk 
        from dbo.country
       )
      select population,continent from result where rnk=3 group by continent,population  ;

If you want the 2nd highest population then enter the rnk in where clause as 2 and so on ..

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One option to do this is using variables. 

select code,name,continent,population 
from (
select c.*, 
@prevPopulation:=@curPopulation,
@curPopulation:=population,
@prevContinent:=@curContinent,
@curContinent:=continent,
case when @curContinent = @prevContinent and @prevPopulation <> @curPopulation then @rn:=@rn+1
     when @curContinent = @prevContinent and @prevPopulation = @curPopulation then @rn:=@rn
else @rn:=1 end as rank
from country c,
(select @rn := 0, @curContinent := '',@prevContinent := '', @curPopulation:=0,@prevPopulation:=0) r
order by continent,population desc
) x
where rank = 3 --replace it with the nth highest value needed

This query uses 4 variables

1) @curContinent which is initially set to an empty string. Thereafter the select assigns the current row's continent.

2) @prevContinent which is initially set to an empty string. Thereafter the select sets it to @curContinent value (which is an empty string initially).

3) @curPopulation which is initially set to 0. Thereafter the select assigns the current row's population.

4) @prevPopulation which is initially set to 0. Thereafter the select sets it to the @curPopulation (which is 0 for the first time and so on).

order by clause is important here to desingate the current and previous rows based on continent and population. This would handle ties too as it would assign the same rank to all the countries within a continent with the same population.

Run the inner query initially to see how the variables are set, which would clarify things for you.

Sample Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming no countries have the same population, then one method is to count the number of countries that have the same or higher population and see when the count is 3:

select c.*
from country c
where (select count(*)
       from country c2
       where c2.continent = c.continent and c2.population >= c.population
      ) = 3;

Upvotes: 1

Related Questions