UPD: 2017-03-31, la versión 5.7.5 de MySQL hizo el conmutador ONLY_FULL_GROUP_BY habilitado por defecto (por lo tanto, el GRUPO no determinista POR consultas se deshabilitó). Además, actualizaron el GRUPO POR implementación y es posible que la solución ya no funcione como se esperaba, incluso con el conmutador deshabilitado. Hay que comprobarlo.

La solución de Bill Karwin anterior funciona bien cuando el número de elementos dentro de los grupos es bastante pequeño, pero el rendimiento de la consulta se vuelve malo cuando los grupos son bastante grandes, ya que la solución requiere aproximadamente n*n/2 + n/2 de solo IS NULL comparaciones.

Hice mis pruebas en una tabla InnoDB de 18684446 filas con 1182 grupos. La tabla contiene los resultados de las pruebas funcionales y tiene la clave principal (test_id, request_id). Así, test_id es un grupo y yo estaba buscando el último request_id para cada test_id.

La solución de Bill ya se ha estado ejecutando durante varias horas en mi dell e4310 y no sé cuándo va a terminar a pesar de que funciona en un índice de cobertura (por lo tanto using index en EXPLICAR).

Tengo un par de otras soluciones que se basan en las mismas ideas:

• si el índice subyacente es el índice BTREE (que suele ser el caso), el mayor par (group_id, item_value) es el último valor dentro de cada group_id, que es el primero para cada group_id si caminamos por el índice en orden descendente;
• si leemos los valores que están cubiertos por un índice, los valores se leen en el orden del índice;
• cada índice implícitamente contiene columnas de clave primaria anexas a eso (que es la clave primaria está en el índice de cobertura). En soluciones a continuación, opero directamente en la clave primaria, en su caso, solo tendrá que agregar columnas de clave primaria en el resultado.
• en muchos casos es mucho más barato recopilar los id de fila requeridos en el orden requerido en una subconsulta y unir el resultado de la subconsulta en el id. Dado que para cada fila en el resultado de subconsulta MySQL necesitará una única recuperación basada en la clave primaria, la subconsulta se colocará primero en la combinación y las filas se mostrarán en el orden de los ID de la subconsulta (si omitimos el ORDEN explícito POR para la combinación)

3 formas en que MySQL usa indexes es un gran artículo para entender algunos detalles.

Solución 1

Este es increíblemente rápido, tarda unos 0,8 segundos en mis filas de 18 M+:

SELECT test_id, MAX(request_id), request_id
FROM testresults
GROUP BY test_id DESC;

Si desea cambiar el orden a ASC, póngalo en una subconsulta, devuelva solo los ID y úselo como la subconsulta para unirse al resto de las columnas:

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id), request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

Este toma alrededor de 1,2 segundos en mis datos.

Solución 2

Aquí hay otra solución que toma alrededor de 19 segundos para mi tabla:

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

Devuelve las pruebas en orden descendente también. Es mucho más lento ya que hace un escaneo de índice completo, pero está aquí para darle una idea de cómo generar N filas máximas para cada grupo.

La desventaja de la consulta es que su resultado no se puede almacenar en caché por la caché de consulta.

Use su subconsulta para devolver la agrupación correcta, porque está a mitad de camino.

Prueba esto:

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

Si no es id quieres el máximo de:

select
    a.*
from
    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on
        a.name = b.name
        and a.other_col = b.other_col

De esta manera, evitará subconsultas correlacionadas y/o pedidos en sus subconsultas, que tienden a ser muy lentos/ineficientes.

Llegué a una solución diferente, que es obtener los ID del último mensaje dentro de cada grupo, luego seleccionar de la tabla mensajes utilizando el resultado de la primera consulta como argumento para una construcción WHERE x IN:

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

No se cómo funciona esto en comparación con algunas de las otras soluciones, pero funcionó espectacularmente para mi tabla con más de 3 millones de filas. (4 segundos de ejecución con más de 1200 resultados)

Esto debería funcionar tanto en MySQL como en SQL Server.

Solución por sub consulta enlace de violín

select * from messages where id in
(select max(id) from messages group by Name)

Solución Por condición de unión enlace de violín

select m1.* from messages m1 
left outer join messages m2 
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null

La razón de este post es dar enlace violín solamente. El mismo SQL ya se proporciona en otras respuestas.

Todavía no he probado con DB grande, pero creo que esto podría ser más rápido que unir tablas:

SELECT *, Max(Id) FROM messages GROUP BY Name

Aquí hay dos sugerencias. Primero, si mysql soporta ROW_NUMBER(), es muy simple:

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ROW_NUMBER() OVER (
      PARTITION BY Name
      ORDER BY Id DESC
    ) AS rk
  FROM messages
)
  SELECT Id, Name, OtherColumns
  FROM messages
  WHERE rk = 1;

Asumo que por "último" quieres decir último en orden de identificación. Si no, cambie la cláusula ORDER BY de la ventana ROW_NUMBER() en consecuencia. Si ROW_NUMBER() no está disponible, esta es otra solución:

Segundo, si no lo hace, esta es a menudo una buena manera de proceder:

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

En otras palabras, seleccione los mensajes donde no hay mensaje later-Id con el mismo nombre.

Aquí está mi solución:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;

Aquí hay otra forma de obtener el último registro relacionado usando GROUP_CONCAT con order by y SUBSTRING_INDEX para elegir uno de los registros de la lista

SELECT 
  `Id`,
  `Name`,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      `Other_Columns` 
      ORDER BY `Id` DESC 
      SEPARATOR '||'
    ),
    '||',
    1
  ) Other_Columns 
FROM
  messages 
GROUP BY `Name` 

La consulta anterior agrupará todos los Other_Columns que están en el mismo grupo Name y usando ORDER BY id DESC unirá todos los Other_Columns en un grupo específico en orden descendente con el separador proporcionado en mi caso he usado ||, usando SUBSTRING_INDEX sobre esta lista elegirá el primero

Fiddle Demo

SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;

Prueba esto:

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
       INNER JOIN (SELECT *
                   FROM   (SELECT `title`,
                                  catid,
                                  `created`,
                                  introtext
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = jos_categories.id )  

Puedes ver desde aquí también.

Http://sqlfiddle.com/#! 9 / ef42b/9

PRIMERA SOLUCIÓN

SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);

SEGUNDA SOLUCIÓN

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;

¿Hay alguna manera de que podamos usar este método para eliminar duplicados en una tabla? El conjunto de resultados es básicamente una colección de registros únicos, por lo que si pudiéramos eliminar todos los registros que no están en el conjunto de resultados, efectivamente no tendríamos duplicados? Intenté esto pero MySQL dio un error 1093.

DELETE FROM messages WHERE id NOT IN
 (SELECT m1.id  
 FROM messages m1 LEFT JOIN messages m2  
 ON (m1.name = m2.name AND m1.id < m2.id)  
 WHERE m2.id IS NULL)

¿Hay alguna manera de tal vez guardar la salida en una variable temporal y luego eliminar de NO EN (variable temporal)? @Bill gracias por una solución muy útil.

EDITAR: Creo que encontré la solución:

DROP TABLE IF EXISTS UniqueIDs; 
CREATE Temporary table UniqueIDs (id Int(11)); 

INSERT INTO UniqueIDs 
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON 
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields  
    AND T1.ID < T2.ID) 
    WHERE T2.ID IS NULL); 

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);

La siguiente consulta funcionará bien según su pregunta.

SELECT M1.* 
FROM MESSAGES M1,
(
 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
 FROM MESSAGES
 GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;

Hi @Vijay Dev si su tabla mensajes contiene Id que es la clave primaria de incremento automático, entonces para obtener la última base de registro en la clave primaria, su consulta debe leer como se muestra a continuación:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId

Si desea la última fila para cada Name, entonces puede dar un número de fila a cada grupo de filas por el Name y ordenar por Id en orden descendente.

QUERY

SELECT t1.Id, 
       t1.Name, 
       t1.Other_Columns
FROM 
(
     SELECT Id, 
            Name, 
            Other_Columns,
    (
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;

SQL Fiddle

Una aproximación con considerable velocidad es la siguiente.

SELECT * 
FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)

Resultado

Id  Name    Other_Columns
3   A   A_data_3
5   B   B_data_2
6   C   C_data_1

¿qué tal esto:

SELECT DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;

Tuve un problema similar (en postgresql tough) y en una tabla de registros de 1M. Esta solución toma 1.7 s vs 44s producido por el que tiene UNIÓN IZQUIERDA. En mi caso tuve que filtrar el corrispondant de su nombre campo contra valores NULOS, lo que resulta en un rendimiento aún mejor por 0.2 secs

Claramente hay muchas maneras diferentes de obtener los mismos resultados, su pregunta parece ser cuál es una manera eficiente de obtener los últimos resultados en cada grupo en MySQL. Si está trabajando con grandes cantidades de datos y asumiendo que está utilizando InnoDB incluso con las últimas versiones de MySQL (como 5.7.21 y 8.0.4-rc), entonces puede que no haya una forma eficiente de hacer esto.

A veces necesitamos hacer esto con tablas con incluso más de 60 millones de filas.

Para estos ejemplos Utilizaré datos con solo alrededor de 1.5 millones de filas donde las consultas necesitarían encontrar resultados para todos los grupos en los datos. En nuestros casos reales, a menudo necesitaríamos devolver datos de aproximadamente 2,000 grupos (lo que hipotéticamente no requeriría examinar mucho de los datos).

Usaré las siguientes tablas:

CREATE TABLE temperature(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  groupID INT UNSIGNED NOT NULL, 
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 
  PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id)); 

La tabla de temperaturas está poblada con cerca de 1,5 millones de registros aleatorios, y con 100 grupos diferentes. El selected_group se rellena con esos 100 grupos (en nuestros casos esto normalmente sería menos del 20% para todos los grupos).

Como estos datos son aleatorios, significa que varias filas pueden tener las mismas marcas de tiempo registradas. Lo que queremos es obtener una lista de todos los grupos seleccionados en orden de groupId con el último recordedTimestamp para cada grupo, y si el mismo grupo tiene más de una fila coincidente como esa, entonces el último id coincidente de esas filas.

Si hipotéticamente MySQL tenía una función last () que devolvía valores de la última fila en una cláusula especial ORDER BY entonces simplemente podríamos hacer:

SELECT 
  last(t1.id) AS id, 
  t1.groupID, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;

Que solo necesitaría examinar unas 100 filas en este caso, ya que no utiliza ninguna de las funciones normales de GRUPO POR. Esto se ejecutaría en 0 segundos y, por lo tanto, sería altamente eficiente. Tenga en cuenta que normalmente en MySQL veríamos una cláusula ORDER BY después de la cláusula GROUP BY sin embargo esta cláusula ORDER BY se utiliza para determinar el ORDEN de la función last (), si estaba después del GRUPO PARA entonces sería ordena los GRUPOS. Si no hay cláusula GROUP BY presente, los últimos valores serán los mismos en todas las filas devueltas.

Sin embargo MySQL no tiene esto, así que echemos un vistazo a diferentes ideas de lo que tiene y probemos que ninguna de ellas es eficiente.

Ejemplo 1

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT t2.id
  FROM temperature t2 
  WHERE t2.groupID = g.id
  ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  LIMIT 1
);

Esto examinó 3,009,254 filas y tomó ~0.859 segundos en 5.7.21 y un poco más en 8.0.4-rc

Ejemplo 2

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
INNER JOIN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON t5.id = t1.id;

Esto examinó 1,505,331 filas y tomó ~1.25 segundos en 5.7.21 y un poco más en 8.0.4-rc

Ejemplo 3

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
WHERE t1.id IN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
)
ORDER BY t1.groupID;

Esto examinó 3,009,685 filas y tomó ~1.95 segundos en 5.7.21 y un poco más en 8.0.4-rc

Ejemplo 4

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT max(t2.id)
  FROM temperature t2 
  WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
      SELECT max(t3.recordedTimestamp)
      FROM temperature t3 
      WHERE t3.groupID = g.id
    )
);

Esto examinó 6,137,810 filas y tomó ~2.2 segundos en 5.7.21 y un poco más en 8.0.4-rc

Ejemplo 5

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM (
  SELECT 
    t2.id, 
    t2.groupID, 
    t2.recordedTimestamp, 
    t2.recordedValue, 
    row_number() OVER (
      PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
    ) AS rowNumber
  FROM selected_group g 
  INNER JOIN temperature t2 ON t2.groupID = g.id
) t1 WHERE t1.rowNumber = 1;

Esto examinó 6,017,808 filas y tomó ~4.2 segundos en 8.0.4-rc

Ejemplo 6

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM (
  SELECT 
    last_value(t2.id) OVER w AS id, 
    t2.groupID, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID = g.id
  WINDOW w AS (
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, t2.id 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
) t1
GROUP BY t1.groupID;

Esto examinó 6,017,908 filas y tomó ~17.5 segundos en 8.0.4-rc

Ejemplo 7

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2 
  ON t2.groupID = g.id 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
  )
WHERE t2.id IS NULL
ORDER BY t1.groupID;

Este tardaba una eternidad, así que tuve que matarlo.

Si el rendimiento es realmente su preocupación, puede introducir una nueva columna en la tabla llamada IsLastInGroup de tipo BIT.

Póngalo en true en las columnas que son últimas y manténgalo con cada fila insert/update/delete. Las escrituras serán más lentas, pero te beneficiarás con las lecturas. Depende de su caso de uso y lo recomiendo solo si está centrado en la lectura.

Así que su consulta se verá como:

SELECT * FROM Messages WHERE IsLastInGroup = 1

select * from messages group by name desc