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

​x
 
A 40 23/56/1982A 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 DISTINCT SUB.GRUPO,SUBG.FECHA,SUBG.VALORFROM T2 SUBGLEFT JOIN T2 SUB2ON SUBG.GRUPO = SUB2.GRUPO AND SUBG.VALOR < SUB2.VALORWHERE SUB2.VALOR is NULLORDER BY GRUP​

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

 
SELECT GRUPO, FECHAFROM T2WHERE 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

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

Advertisement

Answer