SQL seleziona solo le righe con valore massimo su una colonna

Ho questa tabella per i documenti (versione semplificata qui):

+------+-------+--------------------------------------+ | id | rev | content | +------+-------+--------------------------------------+ | 1 | 1 | ... | | 2 | 1 | ... | | 1 | 2 | ... | | 1 | 3 | ... | +------+-------+--------------------------------------+ 

Come seleziono una riga per ID e solo il massimo numero di giri?
Con i dati sopra riportati, il risultato dovrebbe contenere due righe: [1, 3, ...] e [2, 1, ..] . Sto usando MySQL .

Attualmente utilizzo i controlli del ciclo while per rilevare e sovrascrivere i vecchi giri dal set di risultati. Ma è questo l’unico metodo per raggiungere il risultato? Non c’è una soluzione SQL ?

Aggiornare
Come suggeriscono le risposte, c’è una soluzione SQL, e qui una demo di sqlfiddle .

Aggiornamento 2
Ho notato che dopo aver aggiunto lo sqlfiddle sopra, la velocità con cui la domanda è upvoted ha superato la percentuale di upvote delle risposte. Questa non è stata l’intenzione! Il violino si basa sulle risposte, in particolare sulla risposta accettata.

A prima vista…

Tutto ciò di cui hai bisogno è una clausola GROUP BY con la funzione di aggregazione MAX :

 SELECT id, MAX(rev) FROM YourTable GROUP BY id 

Non è mai così semplice, vero?

Ho appena notato che hai bisogno anche della colonna del content .

Questa è una domanda molto comune in SQL: trovare l’intero dato per la riga con un valore massimo in una colonna per un identificatore di gruppo. L’ho sentito molto durante la mia carriera. In realtà, è stata una delle domande a cui ho risposto nell’intervista tecnica del mio attuale lavoro.

In realtà, è così comune che la community di StackOverflow ha creato un singolo tag solo per rispondere a domande del genere: greatest-n-per-group .

Fondamentalmente, hai due approcci per risolvere questo problema:

Partecipare con group-identifier, max-value-in-group semplice group-identifier, max-value-in-group

In questo approccio, per prima cosa trovi l’ group-identifier, max-value-in-group (già risolto sopra) in una sottoquery. Quindi aggiungi la tua tabella alla sottoquery con uguaglianza su entrambi group-identifier e max-value-in-group :

 SELECT a.id, a.rev, a.contents FROM YourTable a INNER JOIN ( SELECT id, MAX(rev) rev FROM YourTable GROUP BY id ) b ON a.id = b.id AND a.rev = b.rev 

Sinistra Unirsi con se stessi, modificare condizioni e filtri

In questo approccio, hai lasciato aderire al tavolo con se stesso. L’uguaglianza, ovviamente, va group-identifier . Quindi, 2 mosse intelligenti:

  1. La seconda condizione di join ha il valore del lato sinistro inferiore al valore corretto
  2. Quando esegui il passaggio 1, la riga (s) che effettivamente ha il valore massimo avrà NULL nella parte destra (è un LEFT JOIN , ricorda?). Quindi, filtriamo il risultato unito, mostrando solo le righe in cui il lato destro è NULL .

Quindi finisci con:

 SELECT a.* FROM YourTable a LEFT OUTER JOIN YourTable b ON a.id = b.id AND a.rev < b.rev WHERE b.id IS NULL; 

Conclusione

Entrambi gli approcci portano esattamente lo stesso risultato.

Se hai due righe con il max-value-in-group per group-identifier , entrambe le righe saranno nel risultato in entrambi gli approcci.

Entrambi gli approcci sono compatibili ANSI SQL, quindi funzioneranno con il tuo RDBMS preferito, indipendentemente dal suo "sapore".

Entrambi gli approcci sono anche ottimizzati per le prestazioni, tuttavia il tuo chilometraggio può variare (RDBMS, struttura DB, indici, ecc.). Quindi quando scegli un approccio rispetto all'altro, benchmark . E assicurati di scegliere quello che ha più senso per te.

La mia preferenza è usare il minor numero ansible di codice …

Puoi farlo usando IN prova questo:

 SELECT * FROM t1 WHERE (id,rev) IN ( SELECT id, MAX(rev) FROM t1 GROUP BY id ) 

a mio avviso è meno complicato … più facile da leggere e mantenere.

Un’altra soluzione è usare una sottoquery correlata:

 select yt.id, yt.rev, yt.contents from YourTable yt where rev = (select max(rev) from YourTable st where yt.id=st.id) 

Avere un indice su (id, rev) rende la subquery quasi come una semplice ricerca …

Di seguito sono riportati i confronti con le soluzioni nella risposta di @ AdrianCarneiro (subquery, leftjoin), basata su misurazioni MySQL con tabella InnoDB di record di ~ 1 milione, dimensione del gruppo: 1-3.

Mentre per la scansione completa della tabella subquery / leftjoin / correlated timing si riferiscono l’un l’altro come 6/8/9, quando si tratta di ricerche dirette o batch ( id in (1,2,3) ), la subquery è molto più lenta delle altre ( A causa di rieseguire la subquery). Tuttavia non ho potuto differenziare tra leftjoin e soluzioni correlate in velocità.

Un’ultima nota, dato che leftjoin crea n * (n + 1) / 2 join in gruppi, le sue prestazioni possono essere pesantemente influenzate dalla dimensione dei gruppi …

Non posso garantire per la performance, ma ecco un trucco ispirato alle limitazioni di Microsoft Excel. Ha alcune buone caratteristiche

ROBA BUONA

  • Dovrebbe forzare il ritorno di un solo “record massimo” anche se c’è un pareggio (a volte utile)
  • Non richiede un join

APPROCCIO

È un po ‘brutto e richiede che tu sappia qualcosa sull’intervallo di valori validi della colonna rev . Supponiamo di sapere che la colonna rev è un numero compreso tra 0,00 e 999 compresi i decimali ma che ci saranno sempre solo due cifre a destra del punto decimale (ad es. 34.17 sarebbe un valore valido).

L’essenza della cosa è che si crea una singola colonna sintetica per stringa concatenando / comprimendo il campo di confronto primario insieme ai dati che si desidera. In questo modo, è ansible forzare la funzione di aggregazione MAX () di SQL per restituire tutti i dati (perché è stato inserito in una singola colonna). Quindi devi decomprimere i dati.

Ecco come appare con l’esempio sopra, scritto in SQL

 SELECT id, CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev, SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev FROM (SELECT id, CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col FROM yourtable ) GROUP BY id 

L’impacchettamento inizia forzando la colonna rev a essere un numero di lunghezza di carattere noto indipendentemente dal valore di rev in modo che, ad esempio

  • 3.2 diventa 1003.201
  • 57 diventa 1057.001
  • 923,88 diventa 1923,881

Se lo fai bene, il confronto tra stringhe di due numeri dovrebbe produrre lo stesso “massimo” del confronto numerico dei due numeri ed è facile ricondurlo al numero originale usando la funzione di sottostringa (che è disponibile in una forma o nell’altra praticamente ovunque).

Sono sbalordito che nessuna risposta ha offerto la soluzione per le windows SQL:

 SELECT a.id, a.rev, a.contents FROM (SELECT id, rev, contents, ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank FROM YourTable) a WHERE a.rank = 1 

Aggiunto in SQL standard ANSI / ISO SQL standard: 2003 e successivamente esteso con ANSI / ISO Standard SQL: 2008, le funzioni di finestra (o finestra) sono ora disponibili con tutti i principali fornitori. Esistono più tipi di funzioni di classifica disponibili per gestire un problema di parità: RANK, DENSE_RANK, PERSENT_RANK .

Penso che questa sia la soluzione più semplice:

 SELECT * FROM (SELECT * FROM Employee ORDER BY Salary DESC) AS employeesub GROUP BY employeesub.Salary; 
  • SELEZIONA *: restituisce tutti i campi.
  • FROM Employee: Table cercato su.
  • (SELEZIONA * …) sottoquery: restituisce tutte le persone, ordinate per Salario.
  • GROUP BY employeeub.Salary:: impone la riga stipendio top-ordinata di ciascun dipendente come risultato restituito.

Se ti capita di aver bisogno solo di una riga, è ancora più semplice:

 SELECT * FROM Employee ORDER BY Employee.Salary DESC LIMIT 1 

Penso anche che sia il più semplice da abbattere, capire e modificare per altri scopi:

  • ORDINA PER Dipendente. DESCario formale: ordina i risultati in base allo stipendio, con gli stipendi più alti in primo luogo.
  • LIMITE 1: restituire solo un risultato.

Comprendere questo approccio, risolvere uno di questi problemi simili diventa banale: ottenere dipendenti con il salario più basso (cambiare DESC in ASC), ottenere i primi dieci dipendenti (modificare il LIMITE 1 al LIMIT 10), ordinare per mezzo di un altro campo (modificare ORDINA PER Dipendente. Ordine a ORDINE da parte di Employee.Commission), ecc.

Qualcosa come questo?

 SELECT yourtable.id, rev, content FROM yourtable INNER JOIN ( SELECT id, max(rev) as maxrev FROM yourtable WHERE yourtable GROUP BY id ) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev) 

Dato che questa è la domanda più popolare riguardo a questo problema, ti ripropongo anche un’altra risposta qui:

Sembra che ci sia un modo più semplice per farlo (ma solo in MySQL ):

 select * from (select * from mytable order by id, rev desc ) x group by id 

Si prega di accreditare la risposta dell’utente Bohemian in questa domanda per fornire una risposta così concisa ed elegante a questo problema.

EDIT: sebbene questa soluzione funzioni per molte persone potrebbe non essere stabile nel lungo periodo, poiché MySQL non garantisce che l’istruzione GROUP BY restituirà valori significativi per le colonne non nell’elenco GROUP BY. Quindi usa questa soluzione a tuo rischio

Una terza soluzione che non vedo quasi mai menzionata è MySQL e assomiglia a questo:

 SELECT id, MAX(rev) AS rev , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content FROM t1 GROUP BY id 

Sì, sembra orribile (conversione in stringa e ritorno, ecc.) Ma nella mia esperienza è solitamente più veloce delle altre soluzioni. Forse è solo per i miei casi d’uso, ma l’ho usato su tabelle con milioni di record e molti ID univoci. Forse è perché MySQL è piuttosto brutto nell’ottimizzare le altre soluzioni (almeno nei 5.0 giorni in cui ho trovato questa soluzione).

Una cosa importante è che GROUP_CONCAT ha una lunghezza massima per la stringa che può accumulare. Probabilmente vuoi aumentare questo limite impostando la variabile group_concat_max_len . E tieni presente che questo sarà un limite al ridimensionamento se hai un numero elevato di righe.

Ad ogni modo, quanto sopra non funziona direttamente se il tuo campo di contenuto è già testo. In tal caso probabilmente vorrai usare un separatore diverso, come \ 0 forse. Inoltre, potrai eseguire il limite di group_concat_max_len più rapidamente.

Mi piace utilizzare una soluzione basata su NOT EXIST per questo problema:

 SELECT id, rev FROM YourTable t WHERE NOT EXISTS ( SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev ) 

Se hai molti campi nell’istruzione select e vuoi l’ultimo valore per tutti questi campi attraverso il codice ottimizzato:

 select * from (select * from table_name order by id,rev desc) temp group by id 

Cosa ne pensi di questo:

 select all_fields.* from (select id, MAX(rev) from yourtable group by id) as max_recs left outer join yourtable as all_fields on max_recs.id = all_fields.id 

Lo userei:

 select t.* from test as t join (select max(rev) as rev from test group by id) as o on o.rev = t.rev 

La sottoquery SELECT non è forse troppo efficace, ma nella clausola JOIN sembra essere utilizzabile. Non sono un esperto nell’ottimizzare le query, ma ho provato a MySQL, PostgreSQL, FireBird e funziona molto bene.

È ansible utilizzare questo schema in più join e con la clausola WHERE. È il mio esempio di lavoro (risolvere identico al tuo problema con la tabella “fermo”):

 select * from platnosci as p join firmy as f on p.id_rel_firmy = f.id_rel join (select max(id_obj) as id_obj from firmy group by id_rel) as o on o.id_obj = f.id_obj and p.od > '2014-03-01' 

Viene chiesto ai tavoli con ragazzi e ai record, e richiede meno di 0,01 secondi su una macchina veramente non troppo forte.

Non userei la clausola IN (come sopra menzionata sopra). IN viene dato per l’uso con brevi elenchi di costanti e non per essere il filtro di query creato sulla sottoquery. È perché subquery in IN viene eseguita per ogni record scansionato che può rendere la query prendendo molto tempo moooolto.

Questa soluzione effettua solo una selezione da YourTable, quindi è più veloce. Funziona solo per MySQL e SQLite (per SQLite rimuovi DESC) in base al test su sqlfiddle.com. Forse può essere ottimizzato per lavorare su altre lingue che non conosco.

 SELECT * FROM ( SELECT * FROM ( SELECT 1 as id, 1 as rev, 'content1' as content UNION SELECT 2, 1, 'content2' UNION SELECT 1, 2, 'content3' UNION SELECT 1, 3, 'content4' ) as YourTable ORDER BY id, rev DESC ) as YourTable GROUP BY id 

Ecco un bel modo per farlo

Usa il seguente codice:

 with temp as ( select count(field1) as summ , field1 from table_name group by field1 ) select * from temp where summ = (select max(summ) from temp) 

Mi piace farlo classificando i record di qualche colonna. In questo caso, classifica i valori di rev raggruppati per id . Quelli con rev più alti avranno classifiche inferiori. Quindi il più alto punteggio avrà la classifica di 1.

 select id, rev, content from (select @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num, id, rev, content, @prevValue := id from (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP, (select @rowNum := 1 from DUAL) X, (select @prevValue := -1 from DUAL) Y) TEMP where row_num = 1; 

Non sono sicuro che l’introduzione di variabili renda il tutto più lento. Ma almeno non sto interrogando YOURTABLE due volte.

Se qualcuno sta cercando una versione di Linq, sembra che funzioni per me:

 public static IQueryable LatestVersionsPerBlock(this IQueryable blockVersions) { var max_version_per_id = blockVersions.GroupBy(v => v.BlockId) .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } ); return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) ); } 

Ordinato il campo di rev in ordine inverso e quindi raggruppato per id che ha dato la prima riga di ogni raggruppamento che è quella con il valore di giri più alto.

 SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id; 

Testato su http://sqlfiddle.com/ con i seguenti dati

 CREATE TABLE table1 (`id` int, `rev` int, `content` varchar(11)); INSERT INTO table1 (`id`, `rev`, `content`) VALUES (1, 1, 'One-One'), (1, 2, 'One-Two'), (2, 1, 'Two-One'), (2, 2, 'Two-Two'), (3, 2, 'Three-Two'), (3, 1, 'Three-One'), (3, 3, 'Three-Three') ; 

Ciò ha dato il seguente risultato in MySql 5.5 e 5.6

 id rev content 1 2 One-Two 2 2 Two-Two 3 3 Three-Two 

ecco un’altra soluzione spero che possa aiutare qualcuno

 Select a.id , a.rev, a.content from Table1 a inner join (SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev 

Nessuna di queste risposte ha funzionato per me.

Questo è ciò che ha funzionato per me.

 with score as (select max(score_up) from history) select history.* from score, history where history.score_up = score.max 

SELECT * FROM Employee where Employee.Salary in (selezionare max (salary) dal gruppo Employee da Employe_id) ORDER BY Employee.Salary

Ecco un’altra soluzione per recuperare i record solo con un campo che ha il valore massimo per quel campo. Questo funziona per SQL400, che è la piattaforma su cui lavoro. In questo esempio, i record con il valore massimo nel campo FIELD5 verranno recuperati dalla seguente istruzione SQL.

 SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5 FROM MYFILE A WHERE RRN(A) IN (SELECT RRN(B) FROM MYFILE B WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2 ORDER BY B.FIELD5 DESC FETCH FIRST ROW ONLY) 

NON mySQL , ma per altre persone che trovano questa domanda e utilizzano SQL, un altro modo per risolvere il problema più grande per gruppo è l’utilizzo di Cross Apply in MS SQL

 WITH DocIds AS (SELECT DISTINCT id FROM docs) SELECT d2.id, d2.rev, d2.content FROM DocIds d1 CROSS APPLY ( SELECT Top 1 * FROM docs d WHERE d.id = d1.id ORDER BY rev DESC ) d2 

Ecco un esempio in SqlFiddle

Ho usato il sotto per risolvere un mio problema. Ho creato per la prima volta una tabella temporanea e inserito il valore massimo per ogni ID univoco.

 CREATE TABLE #temp1 ( id varchar(20) , rev int ) INSERT INTO #temp1 SELECT a.id, MAX(a.rev) as rev FROM ( SELECT id, content, SUM(rev) as rev FROM YourTable GROUP BY id, content ) as a GROUP BY a.id ORDER BY a.id 

Ho quindi unito questi valori massimi (# temp1) a tutte le possibili combinazioni id / content. In questo modo, filtro naturalmente le combinazioni di id / contenuti non massimi e rimango con gli unici valori di rev massimo per ciascuno.

 SELECT a.id, a.rev, content FROM #temp1 as a LEFT JOIN ( SELECT id, content, SUM(rev) as rev FROM YourTable GROUP BY id, content ) as b on a.id = b.id and a.rev = b.rev GROUP BY a.id, a.rev, b.content ORDER BY a.id 

Un altro modo per eseguire il lavoro consiste nell’utilizzare la funzione analitica MAX () nella clausola OVER PARTITION

 SELECT t.* FROM ( SELECT id ,rev ,contents ,MAX(rev) OVER (PARTITION BY id) as max_rev FROM YourTable ) t WHERE t.rev = t.max_rev 

L’altra soluzione OVER PARTITION già documentata in questo post è

 SELECT t.* FROM ( SELECT id ,rev ,contents ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank FROM YourTable ) t WHERE t.rank = 1 

Questo SELECT 2 funziona bene su Oracle 10g.

 select * from yourtable group by id having rev=max(rev); 

Questo funziona per me in sqlite3:

 SELECT *, MAX(rev) FROM t1 GROUP BY id 

Con *, ottieni una colonna rev duplicata, ma non è un grosso problema.

 SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;