PadhaiTime Logo
Padhai Time

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:

 

undefined

 

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

 

Expected Output:

undefined

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”

 

undefined

 

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;

 

undefined

and we are done.

 

Bengaluru, India
contact.padhaitime@gmail.com
  • We collect cookies and may share with 3rd party vendors for analytics, advertising and to enhance your experience. You can read more about our cookie policy by clicking on the 'Learn More' Button. By Clicking 'Accept', you agree to use our cookie technology.
    Our Privacy policy can be found by clicking here