SQL Interview Question With Max and Min Function |

[@madarasi360]

Good info keep sharing

[@codinglake]

thanks.

[@nigamsaha6856]

Good information

[@anupamjha827]

We can use first_value

[@codinglake]

Yes you can use, multiple ways you can solve the problem .

[@duraiprabhu4337]

select state,city,min(population),max(population) from city_info group by (State,City);

[@codinglake]

Nice one.

[@sushmitasaha9083]

this approach will give state twice and not distinguish between max and min and will display max value under both the columns (same for min)

[@KRANTHIKUMARMARADA]

with cte as (select c.*, row_number() over (partition by state order by population) minpop , row_number() over (partition by state order by population desc) maxpop from city_info c) select state, min(case when minpop=1 then city end) minpopulation, min(case when maxpop=1 then city end) maxpopulation from cte group by state

[@suhastl3087]

Didnt get the max(case condition) and min(case condition) how did it eliminate null

[@madarasi360]

Group function is eliminated the null value

[@codinglake]

watch full video then you understand the logic. Don't skip video.

[@NikhilKumar-of5ek]

​@@madarasi360 group by is a clause not a function.

[@sachinn5503]

WITH CTE AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY STATE ORDER BY POPULATION ASC) RANK_LOW, ROW_NUMBER() OVER(PARTITION BY STATE ORDER BY POPULATION DESC) RANK_HIGH FROM CITY_INFO) SELECT STATE,CITY,POPULATION FROM CTE WHERE RANK_LOW=1 OR RANK_HIGH=1 ORDER BY STATE,CITY;

[@codinglake]

Nice 👍

[@sreesailampavankalyan1007]

Select state, max(population) as max_population, min(population) as min_population from table Group by state

[@codinglake]

nice one

[@anupamjha827]

You wont get city name

[@saktibiswal6445]

Audio is quite low.