Skip to content
Advertisement

How to use a sub-query to pick out corresponding value for another minimum

I’m trying to find a name of a Community Area that corresponds to the minimum safety score in another column. Current code is

select COMMUNITY_AREA_NAME, SAFETY_SCORE from CHICAGO_PUBLIC_SCHOOLS
where (COMMUNITY_AREA_NAME,SAFETY_SCORE) in (select COMMUNITY_AREA_NAME, min(SAFETY_SCORE) 
from CHICAGO_PUBLIC_SCHOOLS group by COMMUNITY_AREA_NAME)

resulting table

What I want is a table that has COMMUNITY_AREA_NAME defined as WASHINGTON PARK and SAFETY SCORE defined as 1.

COMMUNITY_AREA_NAME SAFETY_SCORE
WASHINGTON PARK 1

Dataset is taken from here: https://data.cityofchicago.org/Education/Chicago-Public-Schools-Progress-Report-Cards-2011-/9xs2-f89t

Any help is appreciated.

Advertisement

Answer

Does this work for you?

    select COMMUNITY_AREA_NAME, min(SAFETY_SCORE) as min_score
    from CHICAGO_PUBLIC_SCHOOLS 
    where SAFETY_SCORE in (select min(SAFETY_SCORE) from CHICAGO_PUBLIC_SCHOOLS)
    group by COMMUNITY_AREA_NAME
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement