LIMITING MAX VALUES IN SQL

Question

I am completely rewriting this question, I just cant crack it

IDB DB2 SQL

(from a Chicago Crime Dataset) Which community area is most crome prone?

When I use this code, it does correctly count and sort the data

select community_area_number as community_area_number, count(community_area_number) as total_area_crime
from chicago_crime_data
group by community_area_number
order by total_area_crime desc;

the problem is, it lists all the data descending, but no matter what MAX statement I use, either in the select or the order by statement, it wont show just the max values. The max values are 43, so I would like to to show both 'community_area_numbers' that have 43.

Instead it shows the entire list.

Here is a screenshot

also, yes I understand I can just do a LIMIT 2 command, but that would be cheating since I manually checked that there are 2 max values, but if this data changed or i didnt know that, it doesnt solve anything

thanks in advance

Answer 1

What you would be looking for is the standard SQL clause FETCH WITH TIES;

select community_area_number, count(*) as total_area_crime
from chicago_crime_data
group by community_area_number
order by total_area_crime desc
fetch first row with ties;

Unfortunately, though, DB2 doesn't support WITH TIES in FETCH FIRST.

The classic way (that is before we had the window functions RANK and DENSE_RANK) is to use a subquery: Get the maximum value, then get all rows with that maximum. I am using a CTE (aka WITH clause) here in order not to have to write everything twice.

with counted as
(
  select community_area_number, count(*) as total_area_crime
  from chicago_crime_data
  group by community_area_number
)
select community_area_number, total_area_crime
from counted
where total_area_crime = (select max(total_area_crime) from counted);

(Please note that this is a mere COUNT(*), because we want to count rows per community_area_number.)