MAX() returning multiple lines

Question

When running the script the same returns not only the maximum value, that is, the longest time, but the 3 times.

SELECT  P.CD_PACIENTE CD_PACIENTE
            ,P.NM_PACIENTE NM_PACIENTE
            ,L.CD_UNID_INT CD_UNID_INT
            ,L.DS_ENFERMARIA DS_ENFERMARIA
            ,L.DS_LEITO DS_LEITO
            ,MAX(MI.HR_MOV_INT) HR_MOV_INT


            FROM ATENDIME A
            JOIN PACIENTE P ON P.CD_PACIENTE = A.CD_PACIENTE
            JOIN MOV_INT MI ON MI.CD_ATENDIMENTO = A.CD_ATENDIMENTO
            JOIN LEITO L ON L.CD_LEITO = MI.CD_LEITO

            WHERE A.DT_ALTA_MEDICA IS NULL
            AND P.CD_PACIENTE = 0

            GROUP BY P.CD_PACIENTE 
            ,P.NM_PACIENTE 
            ,L.CD_UNID_INT
            ,L.DS_ENFERMARIA 
            ,L.DS_LEITO

            ORDER BY P.CD_PACIENTE

Returned result:

0   TESTE   35  0319    VIRTUAL     11/06/2018 10:51:18
0   TESTE   23  0414    LEITO03     11/06/2018 10:49:48
0   TESTE   35  0319    LEITO008    01/04/2018 16:00:00

Expected result:

0   TESTE   35  0319    VIRTUAL     11/06/2018 10:51:18

Answer 1

It will bring MAX into your grouping, that’s correct. If you want to look at the MAX of the whole table, just leave MAX in select. Again, if you let the grouping it will always show the maximum result within each grouping. I don’t know if I could be clear.

Answer 2

When you use group by, it makes the grouping in each value of the column you specific, ie group equal values, see in this example.

When you use the max(), it returns the highest value field of the column, ie returns the largest field, see in this example.

If you use the group by together with the max(), the result will return the maximum value of each grouping you ask for specific, not a single value. If you want to take the highest value, remove the grouping of values, to return a single line.

Answer 3

Hello,

As colleagues mentioned above, it is correct to return the three values. The easiest way to get around this, assuming you already have the select ready, is to put it in the from in the form of a subselect and out of it call the MAX function.

Thus:

    SELECT MAX(TAB.HR_MOV_INT) -- demais colunas desejadas
     FROM
    (SELECT  P.CD_PACIENTE CD_PACIENTE
        ,P.NM_PACIENTE NM_PACIENTE
        ,L.CD_UNID_INT CD_UNID_INT
        ,L.DS_ENFERMARIA DS_ENFERMARIA
        ,L.DS_LEITO DS_LEITO
        ,MAX(MI.HR_MOV_INT) HR_MOV_INT

        FROM ATENDIME A
        JOIN PACIENTE P ON P.CD_PACIENTE = A.CD_PACIENTE
        JOIN MOV_INT MI ON MI.CD_ATENDIMENTO = A.CD_ATENDIMENTO
        JOIN LEITO L ON L.CD_LEITO = MI.CD_LEITO

        WHERE A.DT_ALTA_MEDICA IS NULL
        AND P.CD_PACIENTE = 0

        GROUP BY P.CD_PACIENTE 
        ,P.NM_PACIENTE 
        ,L.CD_UNID_INT
        ,L.DS_ENFERMARIA 
        ,L.DS_LEITO) tab