occurrence - PostgreSQL MAX y GROUP BY

sql select first record of each group

Question

Tengo una mesa con id , year y count .

Quiero obtener el MAX(count) para cada id y mantener el year cuando suceda, así que hago esta consulta:

SELECT id, year, MAX(count)
FROM table
GROUP BY id;

Desafortunadamente, me da un error:

ERROR: la columna "table.year" debe aparecer en la cláusula GROUP BY o usarse en una función agregada

Así que intento:

SELECT id, year, MAX(count)
FROM table
GROUP BY id, year;

Pero entonces, no hace MAX(count) , solo muestra la tabla como está. Supongo que porque al agrupar por year e id , se obtiene el máximo para el id de ese año específico.

Entonces, ¿cómo puedo escribir esa consulta? Quiero obtener el id de MAX(count) y el año cuando eso suceda.

Answer 1

La consulta más corta (y posiblemente más rápida) sería con DISTINCT ON , una extensión de PostgreSQL de la cláusula DISTINCT estándar de SQL:

SELECT DISTINCT ON (1)
       id, count, year
FROM   tbl
ORDER  BY 1, 2 DESC, 3;

Los números se refieren a posiciones ordinales en la lista SELECT . Puedes deletrear los nombres de las columnas para mayor claridad:

SELECT DISTINCT ON (id)
       id, count, year
FROM   tbl
ORDER  BY id, count DESC, year;

El resultado está ordenado por id , que puede o no ser bienvenido. Es mejor que "indefinido" en cualquier caso.

También rompe los lazos (cuando varios años comparten el mismo conteo máximo) de una manera bien definida: elija el primer año. Si no te importa, baja el year del ORDER BY . O elige el último año con el year DESC .

Más explicaciones, enlaces, una referencia y posiblemente soluciones más rápidas en esta respuesta estrechamente relacionada:

• ¿Seleccionar primera fila en cada grupo por grupo?

Aparte: en una consulta de la vida real, no utilizarías algunos de los nombres de columna. id es un anti-patrón no descriptivo para un nombre de columna, count es una palabra reservada en SQL estándar y una función agregada en Postgres.

Answer 2

select *
from (
  select id, 
         year,
         thing,
         max(thing) over (partition by id) as max_thing
  from the_table
) t
where thing = max_thing

o:

select t1.id,
       t1.year,
       t1.thing
from the_table t1
where t1.thing = (select max(t2.thing) 
                  from the_table t2
                  where t2.id = t1.id);

o

select t1.id,
       t1.year,
       t1.thing
from the_table t1
  join ( 
    select id, max(t2.thing) as max_thing
    from the_table t2
    group by id
  ) t on t.id = t1.id and t.max_thing = t1.thing

o (igual que el anterior con una notación diferente)

with max_stuff as (
  select id, max(t2.thing) as max_thing
  from the_table t2
  group by id
) 
select t1.id, 
       t1.year,
       t1.thing
from the_table t1
  join max_stuff t2 
    on t1.id = t2.id 
   and t1.thing = t2.max_thing