Get ID alongside max value ORACLE SQL

Question

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.

first `MAX()` is a aggregate function, which mean you need to use those with `over` or `group by` if select clause have other not aggregate column. pls check [msdn](https://learn.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15) for better info. — T. Peter, Nov 19 '20 at 09:59
second thing is please provide table schema and maybe some sample data etc.which greatly help the process. — T. Peter, Nov 19 '20 at 10:00
@T.Peter will add the table schemas and the data I currently have — , Nov 19 '20 at 10:02

Barbaros Özhan · Answer 1 · 2020-11-19T10:49:57.643

You need MAX() OVER () Analytic function for NUM_RESERVAS column with PARTITION BY TIPOQUARTOID, NUMEROQUARTOANDARID in order to provide grouping for those columns within the partition by list such as

WITH CONTAGEM_QUARTO_POR_ID AS
(
  SELECT q.ID,
         COUNT(r.QUARTOID) AS NUM_RESERVAS,
         q.TIPOQUARTOID,
         q.NUMEROQUARTOANDARID
    FROM RESERVA r
    JOIN QUARTO q
      on q.ID = r.QUARTOID
   GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
), t AS
(
 SELECT t.TIPOQUARTOID, t.NUMEROQUARTOANDARID, t.NUM_RESERVAS, 
        MAX(t.NUM_RESERVAS) 
        OVER (PARTITION BY t.TIPOQUARTOID, t.NUMEROQUARTOANDARID) AS MAX, 
        t.ID
   FROM CONTAGEM_QUARTO_POR_ID t
)
SELECT TIPOQUARTOID, NUMEROQUARTOANDARID, NUM_RESERVAS, ID
  FROM t
 WHERE NUM_RESERVAS = MAX

or more straightforward by using HAVING clause

SELECT q.TIPOQUARTOID,
       q.NUMEROQUARTOANDARID,
       COUNT(r.QUARTOID) AS NUM_RESERVAS,
       q.ID
  FROM RESERVA r
  JOIN QUARTO q
    on q.ID = r.QUARTOID
 GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
 HAVING COUNT(r.QUARTOID) = q.TIPOQUARTOID

score 0 · Accepted Answer · answered Nov 19 '20 at 10:45

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

score 0 · Answer 3 · answered Nov 19 '20 at 12:50

I wouldn't suggest two levels of aggregation. Just use window funtions:

WITH CONTAGEM_QUARTO_POR_ID AS (
      SELECT q.ID, COUNT(*) AS NUM_RESERVAS, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID,
             ROW_NUMBER() OVER (PARTITION BY q.TIPOQUARTOID, q.NUMEROQUARTOANDARID ORDER BY COUNT(*) DESC) as seqnum
      FROM RESERVA r INNER JOIN
           QUARTO q 
           ON q.ID = r.QUARTOID
      GROUP BY q.ID, q.TIPOQUARTOID, q.NUMEROQUARTOANDARID
     )
SELECT cq.*
FROM CONTAGEM_QUARTO_POR_ID cq
WHERE seqnum = 1;

I think this would have slightly better performance than two aggregations (but it is worth checking).

One advantage of this approach is that it is more flexible. If you want ties, just change the ROW_NUMBER() to RANK() in the subquery.

Perhaps more importantly, ROW_NUMBER() is an "idiom" in SQL for returning one row (or a specific number of rows) per group. Learning how to use it is very valuable.

Get ID alongside max value ORACLE SQL

3 Answers3