I currently have the following:
TABLE "QUARTO":
CREATE TABLE Quarto (
Id number(2) NOT NULL,
LotacaoMaxima number(1) NOT NULL,
TipoQuartoId number(1) NOT NULL,
NumeroQuartoNumSequencial number(3) NOT NULL,
NumeroQuartoAndarId varchar2(1) NOT NULL,
PRIMARY KEY (Id));
TABLE RESERVA:
CREATE TABLE Reserva (
Id number(3) NOT NULL,
ClienteNif number(9) NOT NULL,
QuartoId number(2) NOT NULL,
DataInicio date NOT NULL,
DataFim date NOT NULL,
NumPessoas number(1) NOT NULL,
Estado varchar2(15) NOT NULL,
DataCancelamento date,
PRIMARY KEY (Id));
And some data I have in both is:
QUARTO:
| ID | LOTACAOMAXIMA | TIPOQUARTOID | NUMEROQUARTONUMSEQUENCIAL | NUMEROQUARTOANDARID |
| :--- | :--- | :--- | :--- | :--- |
| 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 1 | 2 | 1 |
| 3 | 1 | 1 | 3 | 1 |
| 4 | 1 | 1 | 4 | 1 |
| 5 | 1 | 1 | 5 | 1 |
| 6 | 1 | 1 | 6 | 1 |
| 7 | 1 | 1 | 7 | 1 |
| 8 | 1 | 1 | 8 | 1 |
| 9 | 1 | 1 | 9 | 1 |
| 10 | 1 | 1 | 10 | 1 |
| 11 | 2 | 2 | 11 | 1 |
| 12 | 2 | 2 | 12 | 1 |
| 13 | 2 | 2 | 13 | 1 |
| 14 | 2 | 2 | 14 | 1 |
| 15 | 2 | 2 | 15 | 1 |
| 16 | 2 | 2 | 16 | 1 |
| 17 | 2 | 2 | 17 | 1 |
| 18 | 2 | 2 | 18 | 1 |
| 19 | 2 | 2 | 19 | 1 |
| 20 | 2 | 2 | 20 | 1 |
RESERVA:
| ID | CLIENTENIF | QUARTOID | DATAINICIO | DATAFIM | NUMPESSOAS | ESTADO | DATACANCELAMENTO |
| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |
| 1 | 296837970 | 11 | 2020-06-01 00:00:00 | 2020-06-12 00:00:00 | 1 | Finalizada | NULL |
| 2 | 275784703 | 17 | 2020-06-13 00:00:00 | 2020-06-21 00:00:00 | 1 | Finalizada | NULL |
| 3 | 220347654 | 11 | 2020-07-07 00:00:00 | 2020-07-15 00:00:00 | 2 | Finalizada | NULL |
| 4 | 294772545 | 12 | 2020-08-01 00:00:00 | 2020-08-15 00:00:00 | 2 | Finalizada | NULL |
| 5 | 220347654 | 3 | 2020-01-01 00:00:00 | 2020-01-16 00:00:00 | 1 | Finalizada | NULL |
WITH CONTAGEM_QUARTO_POR_ID AS (SELECT q.ID, COUNT(r.QUARTOID) AS NUM_RESERVAS, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
FROM RESERVA r
INNER JOIN QUARTO q on q.ID = r.QUARTOID
GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID)
SELECT t.TIPOQUARTOID, t.NUMEROQUARTOANDARID, MAX(t.NUM_RESERVAS) AS MAX
FROM CONTAGEM_QUARTO_POR_ID t
GROUP BY t.TIPOQUARTOID, t.NUMEROQUARTOANDARID
And the Output is the following:
| TIPOQUARTOID | NUMEROQUARTOANDARID | MAX |
| :----------- | :------------------ | :-- |
| 1 | 2 | 2 |
| 2 | 1 | 8 |
| 1 | 1 | 1 |
I want to, alongside the data I currently have, also show toe ID of each row, but when I add the t.ID to the SELECT it forces me to add it to GROUP BY and the output is this:
| TIPOQUARTOID | NUMEROQUARTOANDARID | MAX | ID |
| :----------- | :------------------ | :-- | :- |
| 2 | 1 | 2 | 11 |
| 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 3 |
| 1 | 2 | 2 | 21 |
| 2 | 1 | 1 | 17 |
| 2 | 1 | 1 | 12 |
| 2 | 1 | 8 | 16 |
I only wan to get the max value and the ID associated to that MAX.
You can use the KEEP clause in your query without changing it much as follows:
WITH CONTAGEM_QUARTO_POR_ID AS
(SELECT q.ID, COUNT(r.QUARTOID) AS NUM_RESERVAS, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
FROM RESERVA r
INNER JOIN QUARTO q on q.ID = r.QUARTOID
GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID)
SELECT t.TIPOQUARTOID, t.NUMEROQUARTOANDARID, MAX(t.NUM_RESERVAS) AS MAX,
max(t.ID) keep(dense_rank first order by t.NUM_RESERVAS desc nulls last) as ID -- this
FROM CONTAGEM_QUARTO_POR_ID t
GROUP BY t.TIPOQUARTOID, t.NUMEROQUARTOANDARID