CODEWITHSUNDEEP
sqlsql-servert-sql
logdog
logdog

Reputation: 3

SQL Ranking Based on State and City

Sample Output Image

I have two datasets. First contains State Name, City Name and Other Contains Temperature information. How can I rank by State then rank by city over temperature?

select RANK() over(order by AVG(Average_Temp) desc) State_Rank_Temp, aqs_sites.State_Name, 
       RANK() over(partition by State_Name order by AVG(Average_Temp) desc) City_Rank, aqs_sites.City_Name, AVG(Temperature.Average_Temp) Average 
from aqs_sites INNER JOIN
    Temperature
     ON (aqs_sites.State_Code=Temperature.State_Code AND 
         aqs_sites.County_Code=Temperature.County_Code AND 
         aqs_sites.Site_Number=Temperature.Site_Num)
group by aqs_sites.City_Name, aqs_sites.State_Name;

Upvotes: 0

Views: 536

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Hmmmm. You need an average at the state level before doing the ranking. Here is one method:

select s.State_Name, 
       s.City_Name,
       avg(t.Average_Temp) as average 
       rank() over (partition by s.State_Name order by avg(t.Average_Temp) desc) as City_Rank, 
       dense_rank() over (order by avg(state_average))
from aqs_sites s INNER JOIN
     (select t.*, avg(Average_Temp) over (partition by state_code) as state_average
      from Temperature t
     ) t
     ON s.State_Code = t.State_Code AND 
        s.County_Code = t.County_Code AND 
        s.Site_Number = t.Site_Num
group by s.City_Name, s.State_Name;

Upvotes: 1

Related Questions