Limit rows but increase if the result have values equals

Question

I have two tables: hospede and hospedagem. I'm trying to limit the result to 3 rows with the highest values, not just "limit 3". So if some result of the "sum" have the same value should be increase the maximum result rows.

Current result:

"Sao Paulo";810.40
"Porto Alegre";810.40
"Cascavel";740.40
"Ijui";680.40
"Itapema";540.00

The expected result:

"Sao Paulo";810.40
"Porto Alegre";810.40
"Cascavel";740.40
"Ijui";680.40

My humble query:

select      cidade "Cidade", 
            sum(valorDiaria * (dataSaida - dataEntrada)) "Valor Total"
from        hospedagem h1, hospede h2
where       h1.codHospede = h2.codHospede
and         CURRENT_DATE - interval '3 months' < dataEntrada
group by    "Cidade"
order by    "Valor Total" desc

Answer 1

Use dense_rank() or rank() depending on how you want to handle ties:

select "Cidade", "Valor Total"
from (select cidade as "Cidade", 
             sum(valorDiaria * (dataSaida - dataEntrada)) as  "Valor Total",
             dense_rank() over (order by sum(valorDiaria * (dataSaida - dataEntrada)) desc) as seqnum
      from hospedagem h1 join hospede h2
           on h1.codHospede = h2.codHospede
      where CURRENT_DATE - interval '3 months' < dataEntrada
      group by "Cidade"
     ) hh
where seqnum <= 3;

If your data is:

100
100
100
100
 90
 90
 80
 79

Then dense_rank() will return the first seven rows. rank() will only return the first four. row_number() would return the first 3.