una - seleccionar valores maximos sql

MySQL: seleccione N filas, pero con solo valores únicos en una columna

Question

Dado este conjunto de datos:

ID Name City Birthyear
1 Egon Spengler New York 1957
2 Mac Taylor New York 1955
3 Sarah Connor Los Angeles 1959
4 Jean-Luc Picard La Barre 2305
5 Ellen Ripley Nostromo 2092
6 James T. Kirk Riverside 2233
7 Henry Jones Chicago 1899

Necesito encontrar las 3 personas mayores, pero solo una de cada ciudad.

Si fuera solo los tres más antiguos, sería ...

• Henry Jones / Chicago
• Mac Taylor / Nueva York
• Egon Spengler / Nueva York

Sin embargo, dado que tanto Egon Spengler como Mac Taylor están ubicados en Nueva York, Egon Spengler se retiraría y el siguiente (Sarah Connor / Los Angeles) entraría en su lugar.

¿Alguna solución elegante?

Actualizar:

Actualmente, una variación de PConroy es la mejor / más rápida solución:

SELECT P.*, COUNT(*) AS ct
FROM people P
JOIN (SELECT MIN(Birthyear) AS Birthyear
FROM people
GROUP by City) P2 ON P2.Birthyear = P.Birthyear
GROUP BY P.City
ORDER BY P.Birthyear ASC
LIMIT 10;

Su consulta original con "IN" es extremadamente lenta con grandes conjuntos de datos (abortada después de 5 minutos), pero mover la subconsulta a un JOIN acelerará mucho. Tomó aproximadamente 0.15 segundos por aprox. 1 mio filas en mi entorno de prueba. Tengo un índice en "Ciudad, año de nacimiento" y un segundo justo en "Año de nacimiento".

Nota: Esto está relacionado con ...

• Seleccionar filas únicas en un conjunto de dos posibilidades
• Consulta SQL para obtener el último precio

Answer 1

¿Algo como eso?

SELECT
Id, Name, City, Birthyear
FROM
TheTable
WHERE
Id IN (SELECT TOP 1 Id FROM TheTable i WHERE i.City = TheTable.City ORDER BY Birthyear)

Answer 2

@BlaM

ACTUALIZADO acaba de encontrar que es bueno usar USING en lugar de ON. eliminará las columnas duplicadas en el resultado.

SELECT P.*, COUNT(*) AS ct
FROM people P
JOIN (SELECT City, MIN(Birthyear) AS Birthyear
FROM people
GROUP by City) P2 USING(Birthyear, City)
GROUP BY P.City
ORDER BY P.Birthyear ASC
LIMIT 10;

POSTE ORIGINAL

Hola, he intentado utilizar su consulta actualizada, pero obtenía resultados incorrectos hasta que agregué una condición adicional para unirme (también una columna adicional en la selección de unión). transferido a su consulta, estoy usando esto:

SELECT P.*, COUNT(*) AS ct
FROM people P
JOIN (SELECT City, MIN(Birthyear) AS Birthyear
FROM people
GROUP by City) P2 ON P2.Birthyear = P.Birthyear AND P2.City = P.City
GROUP BY P.City
ORDER BY P.Birthyear ASC
LIMIT 10;

en teoría, no debería necesitar el último GROUP BY P.City, pero lo he dejado allí por ahora, por las dudas. probablemente lo elimine más tarde.

Answer 3

No es bonito, pero debería funcionar también con varias personas con el mismo dob:

Datos de prueba:

select id, name, city, dob
into people
from
(select 1 id,''Egon Spengler'' name, ''New York'' city , 1957 dob
union all select 2, ''Mac Taylor'',''New York'', 1955
union all select 3, ''Sarah Connor'',''Los Angeles'', 1959
union all select 4, ''Jean-Luc Picard'',''La Barre'', 2305
union all select 5, ''Ellen Ripley'',''Nostromo'', 2092
union all select 6, ''James T. Kirk'',''Riverside'', 2233
union all select 7, ''Henry Jones'',''Chicago'', 1899
union all select 8, ''Blah'',''New York'', 1955) a

Consulta:

select
*
from
people p
left join people p1
ON
p.city = p1.city
and (p.dob > p1.dob and p.id <> p1.id)
or (p.dob = p1.dob and p.id > p1.id)
where
p1.id is null
order by
p.dob

Answer 4

Probablemente esta no sea la solución más elegante y rápida, pero debería funcionar. Estoy ansioso por ver las soluciones de los gurús de bases de datos reales.

select p.* from people p,
(select city, max(age) as mage from people group by city) t
where p.city = t.city and p.age = t.mage
order by p.age desc

Answer 5

Probablemente no sea la más elegante de las soluciones, y el rendimiento de IN puede sufrir en tablas más grandes.

La consulta anidada obtiene el año de Birthyear mínimo para cada ciudad. Solo los registros que tienen este año de Birthyear coinciden en la consulta externa. Ordenar por edad y luego limitar a 3 resultados te da las 3 personas mayores que también son las más viejas en su ciudad (Egon Spengler se retira ...)

SELECT Name, City, Birthyear, COUNT(*) AS ct
FROM table
WHERE Birthyear IN (SELECT MIN(Birthyear)
FROM table
GROUP by City)
GROUP BY City
ORDER BY Birthyear DESC LIMIT 3;
+-----------------+-------------+------+----+
| name | city | year | ct |
+-----------------+-------------+------+----+
| Henry Jones | Chicago | 1899 | 1 |
| Mac Taylor | New York | 1955 | 1 |
| Sarah Connor | Los Angeles | 1959 | 1 |
+-----------------+-------------+------+----+

Editar - se agregó GROUP BY City a la consulta externa, ya que las personas con los mismos años de nacimiento devolverían varios valores. Agrupar en la consulta externa garantiza que solo se devolverá un resultado por ciudad, si más de una persona tiene ese año de Birthyear mínimo. La columna ct mostrará si hay más de una persona en la ciudad con ese año de Birthyear