How to Bring the 2 highest values of each different id - SQL

Question

I put together a spreadsheet to facilitate the understanding of my doubt.

How to select only the 2 "two" largest room house tuition?

I only managed to bring the highest value of each room, I wanted to bring the 2 highest monthly fees of each room. Someone could give me a light?

SELECT E.ID_SALA, 
       (SELECT MAX(E2.MENSALIDADE)
       FROM ESCOLA E2
       WHERE E2.ID_SALA  = E.ID_SALA)
       AS MAIOR_VALOR
FROM ESCOLA E
GROUP BY E.ID_SALA
ORDER BY E.ID_SALA;

Bank example

Query result I’m trying to get

Answer 1

Use the function DENSE_RANK to obtain the desired result:

SELECT x.id_sala,
       x.mensalidade
  FROM (SELECT *,
               DENSE_RANK() OVER(PARTITION BY e.id_sala ORDER BY e.mensalidade DESC) AS colocacao
          FROM escola e) x
  WHERE x.colocacao <= 2
  ORDER BY x.colocacao

In the example above the PARTITION BY will separate the numbering that will return in the column colocacao for id_sala with an ordination of 1 ~ N. Soon after this, Filter by the placements smaller or equal to 2, thus obtaining the 2 largest.

---

DENSE_RANK

DENSE_RANK computes the rank of a Row in an ordered group of Rows and Returns the rank as a NUMBER.

In free translation:

DENSE_RANK calculates the classification of a row in an ordered group of lines and returns the classification as NUMBER.