Tengo una tabla en la que necesito devolver un valor distinto para cada ID en función del valor de referencia más alto . Como ejemplo, esta tabla:

my table
|--------|----------|-------------|
| ID     | Amount   | Ref_value   |
|---------------------------------|
| 1      | 200      | 5           |
| 1      | 120      | 8           |
| 2      | 150      | 3           |
| 3      | 200      | 4           |
|--------|----------|-------------|

Necesito obtener una devolución por ID, pero como ID = 1 aparece más de una vez, necesito seleccionar la que tenga el "Valor_ref." Más alto. Entonces mi resultado sería:

result of the query over my table
|--------|----------|-------------|
| ID     | Amount   | Ref_value   |
|---------------------------------|
| 1      | 120      | 8           |
| 2      | 150      | 3           |
| 3      | 200      | 4           |
|--------|----------|-------------|
1
JD Gamboa 15 ene. 2018 a las 22:43

3 respuestas

La mejor respuesta

Podría usar una unión interna en el valor máximo agrupado por id

select * from my_table 
inner join (
   select id, max(ref_value) max_value
   from my_table 
   group by id
)  t on t.id = my_table_id, t.max_vale = my_table_value
1
scaisEdge 15 ene. 2018 a las 19:46

Si su base de datos admite funciones de ventana, esta solución accederá a la tabla solo una vez:

SELECT id, amount, ref_value
FROM (
  SELECT t.*, row_number() OVER (PARTITION BY id ORDER BY ref_value DESC) rn
  FROM t
) t
WHERE rn = 1

Si usa Oracle, puede usar esta solución que, en la mayoría de los casos, he encontrado que es un poco más rápida:

SELECT
  max(id)        KEEP (DENSE_RANK FIRST ORDER BY ref_value DESC) id,
  max(amount)    KEEP (DENSE_RANK FIRST ORDER BY ref_value DESC) amount,
  max(ref_value) KEEP (DENSE_RANK FIRST ORDER BY ref_value DESC) ref_value,
FROM t
GROUP BY id

Ambas soluciones anteriores devolverán una fila arbitraria si dos valores ref_value están vinculados.

Si no se aplica nada de lo anterior, la solución de scaisEdge funciona en todas las bases de datos.

1
Lukas Eder 15 ene. 2018 a las 19:54

Seleccionar * podría arrojar un error de nombres de columna ambiguo. Por lo tanto, es posible que desee seleccionar las únicas columnas requeridas allí.

SELECT A.ID, A.AMOUNT, A.REF_VALUE
FROM
MYTABLE A
INNER JOIN
(SELECT ID, MAX(REF_VALUE) AS MAX_REF FROM MYTABLE GROUP BY ID) B
ON A.ID = B.ID AND A.REF_VALUE = B.MAX_REF;
1
Vash 15 ene. 2018 a las 19:57