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)
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