Select multiple rows with highest rank in mysql

Question

I am using MySQL.

From these two tables, I need to select all room(s) that have the highest number of nurses allocated per bed. This can be only one room or more than one if there is a tie.

Table Allocation

+-------+---------+
| nurse |  room   |
+-------+---------+
|911923 |    1    |
|916923 |    1    |
|931923 |    1    |
|931926 |    1    |
|931927 |    4    |
|931928 |    4    |
+-------+---------+

Table Room

+--------+--------+
| number |  size  |
+--------+--------+
|    1   |    2   |
|    4   |    1   |
+-------+---------+

I am trying to select the row(s) with the highest rank, but Limit 1 only limits for one value, in this example both rooms have the same rank. How can I select all rows with the highest rank, if multiple rows have the same rank?

SELECT ROOM.number,
    (SELECT COUNT(*) FROM ALLOCATION
     WHERE ALLOCATION.room = ROOM.number) / ROOM.size AS nurses_per_bed, 
     DENSE_RANK() OVER (ORDER BY nurses_per_bed DESC) AS SEQ

FROM ROOM
LIMIT 1

Answer 1

Step by step:

1. Aggregate allocations per room in order to get the rooms' numbers of nurses.
2. Join rooms and nurse counts (i.e. allocation aggregate results).
3. Rank the resulting rows by ratio.
4. Show only ranked #1 rows.

The query:

select room, nurses, ratio
from
(
  select
    r.room,
    a.nurses,
    a.nurses / r.size as ratio,
    dense_rank() over (order by a.nurses / r.size) as rnk
  from room r
  join
  (
    select number as room, count(*) as nurses
    from allocation
    group by number
  ) a on a.room = r.room
) ranked
where rnk = 1
order by room;