SQL - return coumn value based on max column count-CodePudding

I have an SQL query where I can look up the maximum occurrence of a column. It works fine for what I needed, but now need to expand on this to only return the name of the column.

There's likely a way to improve my original query but I haven't been able to work it out.

select COMMUNITY_AREA_NUMBER, count(*) as CRIMES
    from CHICAGO_CRIME_DATA
    group by COMMUNITY_AREA_NUMBER
    order by CRIMES desc
    limit 1

My basic question is, how can I re-write this to only return the COMMUNITY_AREA_NUMBER?

I've tried to restructure as:

select COMMUNITY_AREA_NUMBER from CHICAGO_CRIME_DATA
    where count(*) as CRIMES group by COMMUNITY_AREA_NUMBER order by CRIMES desc limit 1

I've also tried to incorporate a MAX function, but I don't seem to be getting anywhere with it.

edit: Apparently I've just learned something inner queries. I needed to keep my original query and create an outer query that request something from that result.

select COMMUNITY_AREA_NUMBER from
    (select COMMUNITY_AREA_NUMBER, count(*) as CRIMES
    from CHICAGO_CRIME_DATA
group by COMMUNITY_AREA_NUMBER
order by CRIMES desc
limit 1)

CodePudding user response：

Is this what you want?

SELECT COMMUNITY_AREA_NUMBER
FROM CHICAGO_CRIME_DATA
GROUP BY COMMUNITY_AREA_NUMBER
ORDER BY COUNT(*) DESC
LIMIT 1;

This returns a single community number having the most crime records. I have simply moved COUNT(*) from the select clause, where you don't want it, to the ORDER BY clause.