CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
hashir
hashir

Reputation: 197

How to include a column in a group by without unwanted values

Okay, this might sound lame but what I am going to ask may look a bit kiddish. But still I would always prefer to spill my heart out here than anywhere else.

So I have a table called TEMP_CALC which is as follows :

  COUNTRY   CITY         TEMP
=============================
   US      Arizona       51.7
   US      California    56.7
   US      Bullhead City 51.1
   India   Jaisalmer     42.4
   Libya   Aziziya       57.8
   Iran    Lut Desert    70.7
   India   Banda         42.4

So, the table consists of sample data for some temperature ratings in regions (just some test data).

What I want is a query that would display the cities with maximum temperatures along with their country name and the temperature rating itself.

The query I made would either give me the Country name and maximum temperature or gives me all the rows in the table. What I tried so far is as follows:

This gives me only COUNTRY and TEMP - 

****************************
select country,max(temp) 
from temp_calc
group by country

And when I try to include the city name attached to these temperatures then it gives me all the rows in the table -

*************************
select country,city,max(temp) 
from temp_calc
group by country,city

I want the city with the maximum temperature along with the country name and the temperature rating itself.

Upvotes: 3

Views: 82

Answers (3)

Raghavendra Kedlaya
Raghavendra Kedlaya

Reputation: 116

select * from (
select *,  ROW_NUMBER() over (partition by country order by country,temp desc) as sortid, 
 from temp_calc  ) t 
 where sortid =1

Upvotes: 1

user330315
user330315

Reputation:

If I understand you correctly, you want the city with the highest temperature for each country. This is typically done using window functions:

select country,city,temp
from (
  select country,city,temp, 
         row_number() over (partition by country order by temp desc) as rn
  from temp_calc
) t 
where rn = 1
order by country, city;

Upvotes: 3

Assafs
Assafs

Reputation: 3275

You have the solution in your first try: 

select country,max(temp) 
from temp_calc
group by country

You just need to add the corresponding city to the result set. You could do this:

 with max_temp as (select country, max(temp) m_temp 
    from temp_calc
    group by country)
 select country, city, temp 
 from temp_calc where 
 temp=max_temp.m_temp and 
 country=max_temp.country;

This should allow you to filter the results from the table based on the maximum temps.

Upvotes: 1

Related Questions