How to include a column in a group without unwanted values
Ok, this might sound lame, but what I'm about to ask might look a little childish. But still, I would always rather spill my heart here than anywhere else.
So, I have a table named TEMP_CALC that looks like this:
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 estimates in the regions (only some test data).
What I want is a query that displays the cities with maximum temperatures along with their country name and the estimated temperature itself.
The query I made would give me the country name and maximum temperature, or give me all the rows in the table. I've tried the following so far:
This only gives me 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 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 name of the country and the temperature estimate itself.
source to share
If I understand you correctly, you want the city with the highest temperature for each country. This is usually 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;
source to share
You have a solution first of all:
select country,max(temp)
from temp_calc
group by country
You just need to add the appropriate city to the result set. You can do it:
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 will allow you to filter the results from the table based on the fastest rates.
source to share