Get Highest or Lowest record Category wise in SQL

In the last article we have seen on how to get first, second or third highest from the data. But suppose there are multiple categories present in the column and for each category we want the record which has the highest/lowest value for another attribute.

 

In this case, ROW_NUMBER() will be used along with the “Partition By” clause.

 

Suppose data is present in this format:

 

 

Here for each Country, we want the state which has the highest population.

 

Expected Output:

Query to assign Rank:

SELECT 

            CountryName, 

            StateName, 

            Population,

            ROW_NUMBER() OVER(PARTITION BY CountryName ORDER BY Population Desc) as Rank

FROM Country_Wise_Data;

 

For each country, it will assign the rank. The Highest Populated state will be assigned with Rank 1. If you want Lowest Population state to be assigned as Rank 1, you need to use “Order by Population asc”

 

 

Once we have the ranks assigned, we can simply write one more query to filter out and consider the records where rank is 1 only.

 

Query:

  

SELECT 

       CountryName, 

       StateName, 

       Population

FROM 

(

              SELECT 

                          CountryName, 

                          StateName, 

                          Population,

                          ROW_NUMBER() OVER(PARTITION BY CountryName ORDER BY Population Desc) as Rank

              FROM Country_Wise_Data

) as Temp

Where Temp.Rank = 1;

 

and we are done.