Ottieni record con valore massimo per ogni gruppo di risultati SQL raggruppati

Come si ottengono le righe che contengono il valore massimo per ogni gruppo raggruppato?

Ho visto alcune variazioni eccessivamente complicate su questa domanda e nessuna con una buona risposta. Ho cercato di mettere insieme l’esempio più semplice ansible:

Data una tabella simile a quella in basso, con colonne di persone, gruppi e età, in che modo otterresti la persona più anziana in ciascun gruppo? (Un pareggio all’interno di un gruppo dovrebbe dare il primo risultato alfabetico)

Person | Group | Age --- Bob | 1 | 32 Jill | 1 | 34 Shawn| 1 | 42 Jake | 2 | 29 Paul | 2 | 36 Laura| 2 | 39 

Set di risultati desiderati:

 Shawn | 1 | 42 Laura | 2 | 39 

C’è un modo super semplice per farlo in mysql:

 select * from (select * from mytable order by `Group`, age desc, Person) x group by `Group` 

Funziona perché in mysql è ansible non aggregare colonne non di gruppo, nel qual caso mysql restituisce solo la prima riga. La soluzione consiste nel ordinare prima i dati in modo tale che per ogni gruppo la riga desiderata sia la prima, quindi raggruppare in base alle colonne per le quali si desidera il valore.

Evita le subquery complicate che cercano di trovare il max() ecc., E anche i problemi di restituzione di più righe quando ce ne sono più di una con lo stesso valore massimo (come farebbero le altre risposte)

Nota: questa è una soluzione solo mysql . Tutti gli altri database che conosco genereranno un errore di syntax SQL con il messaggio “colonne non aggregate non elencate nella clausola group by” o simili. Poiché questa soluzione utilizza un comportamento non documentato , il più prudente potrebbe voler includere un test per asserire che funzioni ancora se una versione futura di MySQL modificasse questo comportamento.

Aggiornamento della versione 5.7:

Dalla versione 5.7, l’impostazione della sql-mode include ONLY_FULL_GROUP_BY per impostazione predefinita, quindi per fare in modo che funzioni non è necessario disporre di questa opzione (modificare il file di opzioni per il server per rimuovere questa impostazione).

La soluzione corretta è:

 SELECT o.* FROM `Persons` o # 'o' from 'oldest person in group' LEFT JOIN `Persons` b # 'b' from 'bigger age' ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL # bigger age not found 

Come funziona:

Corrisponde a ogni riga da o con tutte le righe da b aventi lo stesso valore nella colonna Group e un valore maggiore nella colonna Age . Qualsiasi riga da o non ha il valore massimo del suo gruppo nella colonna Age corrisponderà a una o più righe da b .

Il LEFT JOIN fa corrispondere la persona più anziana del gruppo (incluse le persone che sono sole nel loro gruppo) con una riga piena di NULL s da b ('non più grande età del gruppo').
L'uso di INNER JOIN rende queste righe non corrispondenti e vengono ignorate.

La clausola WHERE mantiene solo le righe che hanno NULL nei campi estratti da b . Sono le persone più anziane di ciascun gruppo.

Ulteriori letture

Questa soluzione e molti altri sono spiegati nel libro Antipatterns di SQL: evitare le insidie ​​della programmazione di database

La mia soluzione semplice per SQLite (e probabilmente MySQL):

 SELECT *, MAX(age) FROM mytable GROUP BY `Group`; 

Tuttavia non funziona in PostgreSQL e forse in altre piattaforms.

In PostgreSQL puoi usare la clausola DISTINCT ON :

 SELECT DISTINCT ON ("group") * FROM "mytable" ORDER BY "group", "age" DESC; 

Puoi unirti contro una sottoquery che tira MAX(Group) ed Age . Questo metodo è portatile su molti RDBMS.

 SELECT t1.* FROM yourTable t1 INNER JOIN ( SELECT `Group`, MAX(Age) AS max_age FROM yourTable GROUP BY `Group` ) t2 ON t1.`Group` = t2.`Group` AND t1.Age = t2.max_age; 

Utilizzando il metodo di classificazione.

 SELECT @rn := CASE WHEN @prev_grp <> groupa THEN 1 ELSE @rn+1 END AS rn, @prev_grp :=groupa, person,age,groupa FROM users,(SELECT @rn := 0) r HAVING rn=1 ORDER BY groupa,age DESC,person 

la soluzione di axiac è ciò che ha funzionato meglio per me alla fine. Avevo tuttavia una complessità aggiuntiva: un “valore massimo” calcolato, derivato da due colonne.

Usiamo lo stesso esempio: vorrei la persona più anziana in ogni gruppo. Se ci sono persone ugualmente vecchie, prendi la persona più alta.

Ho dovuto eseguire il join sinistro due volte per ottenere questo comportamento:

 SELECT o1.* WHERE (SELECT o.* FROM `Persons` o LEFT JOIN `Persons` b ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL) o1 LEFT JOIN (SELECT o.* FROM `Persons` o LEFT JOIN `Persons` b ON o.Group = b.Group AND o.Age < b.Age WHERE b.Age is NULL) o2 ON o1.Group = o2.Group AND o1.Height < o2.Height WHERE o2.Height is NULL; 

Spero che questo ti aiuti! Immagino che ci dovrebbe essere un modo migliore per farlo anche se ...

Utilizzo di CTE – Common Table Expressions:

 WITH MyCTE(MaxPKID, SomeColumn1) AS( SELECT MAX(a.MyTablePKID) AS MaxPKID, a.SomeColumn1 FROM MyTable1 a GROUP BY a.SomeColumn1 ) SELECT b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 MAX(b.NumEstado) FROM MyTable1 b INNER JOIN MyCTE c ON c.MaxPKID = b.MyTablePKID GROUP BY b.MyTablePKID, b.SomeColumn1, b.SomeColumn2 --Note: MyTablePKID is the PrimaryKey of MyTable 

Non sono sicuro che MySQL abbia la funzione row_number. Se è così, puoi usarlo per ottenere il risultato desiderato. Su SQL Server puoi fare qualcosa di simile a:

 CREATE TABLE p ( person NVARCHAR(10), gp INT, age INT ); GO INSERT INTO p VALUES ('Bob', 1, 32); INSERT INTO p VALUES ('Jill', 1, 34); INSERT INTO p VALUES ('Shawn', 1, 42); INSERT INTO p VALUES ('Jake', 2, 29); INSERT INTO p VALUES ('Paul', 2, 36); INSERT INTO p VALUES ('Laura', 2, 39); GO SELECT t.person, t.gp, t.age FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY gp ORDER BY age DESC) row FROM p ) t WHERE t.row = 1; 

La mia soluzione funziona solo se è necessario recuperare solo una colonna, tuttavia per le mie esigenze è stata la migliore soluzione trovata in termini di prestazioni (utilizza solo una singola query!):

 SELECT SUBSTRING_INDEX(GROUP_CONCAT(column_x ORDER BY column_y),',',1) AS xyz, column_z FROM table_name GROUP BY column_z; 

Usa GROUP_CONCAT per creare una lista concatale ordinata e quindi sottostringa solo alla prima.

Puoi anche provare

 SELECT * FROM mytable WHERE age IN (SELECT MAX(age) FROM mytable GROUP BY `Group`) ; 

Questo metodo ha il vantaggio di permetterti di classificare secondo una colonna diversa e non di cestinare gli altri dati. È abbastanza utile in una situazione in cui si sta tentando di elencare gli ordini con una colonna per gli articoli, elencando prima il più pesante.

Fonte: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

 SELECT person, group, GROUP_CONCAT( DISTINCT age ORDER BY age DESC SEPARATOR ', follow up: ' ) FROM sql_table GROUP BY group; 

che il nome della tabella sia gente

 select O.* -- > O for oldest table from people O , people T where O.grp = T.grp and O.Age = (select max(T.age) from people T where O.grp = T.grp group by T.grp) group by O.grp; 

Se ID (e tutti i couli) è necessario da mytable

 SELECT * FROM mytable WHERE id NOT IN ( SELECT A.id FROM mytable AS A JOIN mytable AS B ON A. GROUP = B. GROUP AND A.age < B.age ) 

Ecco come ottengo le righe N max per gruppo in mysql

 SELECT co.id, co.person, co.country FROM person co WHERE ( SELECT COUNT(*) FROM person ci WHERE co.country = ci.country AND co.id < ci.id ) < 1 ; 

come funziona:

  • auto join al tavolo
  • i gruppi sono fatti da co.country = ci.country
  • N elementi per gruppo sono controllati da ) < 1 quindi per 3 elementi -) <3
  • per ottenere il massimo o il minimo dipende da: co.id < ci.id
    • co.id
    • co.id> ci.id - min

Esempio completo qui:

mysql seleziona n valori massimi per gruppo

Ho una soluzione semplice usando WHERE IN

 SELECT a.* FROM `mytable` AS a WHERE a.age IN( SELECT MAX(b.age) AS age FROM `mytable` AS b GROUP BY b.group ) ORDER BY a.group ASC, a.person ASC 
 with CTE as (select Person, [Group], Age, RN= Row_Number() over(partition by [Group] order by Age desc) from yourtable)` `select Person, Age from CTE where RN = 1` 

Non userei il gruppo come nome di colonna dato che è una parola riservata. Tuttavia, seguendo SQL funzionerebbe.

 SELECT a.Person, a.Group, a.Age FROM [TABLE_NAME] a INNER JOIN ( SELECT `Group`, MAX(Age) AS oldest FROM [TABLE_NAME] GROUP BY `Group` ) b ON a.Group = b.Group AND a.Age = b.oldest