Oracle SQL: how to show only one max per group

Hi everyone first thanks for your time. I have this data

Data:

Data

And I have to obtain the max values of everygroup, if there is one group that has two same max values like

A 40 23/56/1982
A 40 31/4/5521

Only show one of them. Like this

result

Till now I’ve tried two ways and they work only if there is not any repeated max per group.

Oracle SQL code:

SELECT DISTINCT SUB.GRUPO,SUBG.FECHA,SUBG.VALOR
FROM T2 SUBG
LEFT JOIN T2 SUB2
ON SUBG.GRUPO = SUB2.GRUPO AND SUBG.VALOR < SUB2.VALOR
WHERE SUB2.VALOR is NULL
ORDER BY GRUP

SELECT GRUPO, FECHA
FROM T2
WHERE VALOR IN (SELECT MAX(VALOR) FROM T2 GROUP BY GRUPO)
ORDER BY GRUPO

Any ideas how to do it?

Answer

use row_number()

 select * from (select a.*, row_number() over(partition by GRUPO order by VALOR desc) rn
 from T2 a
) a where rn=1

Advertisement

Answer