SQL: Ottenere solo un record con determinate caratteristiche da ogni raggruppamento di un group by
Oggi ho guadagnato una medaglia per aver risolto questo problema:
Data questa tabella:
columnn1 Column2 a 2010-04-15 00:00:00.000 a 2010-04-17 00:00:00.000 b 2010-04-17 00:00:00.000 b 2010-04-15 00:00:00.000 b 2010-04-20 00:00:00.000 c 2010-04-19 00:00:00.000 d 2010-04-12 00:00:00.000 d 2010-04-13 00:00:00.000 |
come si fa ad estrarre, per ogni codice, quello la cui data è più vicina alla data corrente? Ovvero ad avere il risultato:
a 2010-04-15 00:00:00.000 b 2010-04-15 00:00:00.000 c 2010-04-19 00:00:00.000 d 2010-04-13 00:00:00.000 |
La soluzione è:
SELECT column1, column2 FROM prova WHERE (SELECT COUNT(*) FROM prova AS p WHERE p.column1 = prova.column1 AND abs(datediff(DAY, p.column2, getdate())) < abs(datediff(DAY, prova.column2, getdate())) ) = 0 |
Il problema è simile al solito dilemma: dato un group by, come faccio ad estrarre solo i record che hanno valore massimo (o che hanno massima occorrenza o altre condizioni)?
Il risultato è un self-join che in maniera generica può essere scritto così:
select [campi]
from [tabella] t1
where
( select [funzione scalare per la scelta dei campi]
from [tabella] as t2
where t1.key = t2.key
and [condizione di confronto fra due campi (anche calcolati) della tabella]
) [condizione sulla funzione scalare definita]
Il classico esempio: di ogni gruppo vogliamo solo il record di valore massimo
select key, valore
from [tabella] t1
where
( select count(*)
from [tabella] as t2
where t1.key = t2.key
and t2.valore > t1.valore -- prendo il maggiore
) = 0
Ovvero, prendo il record della prima tabella che non ha record con valore superiore sulla seconda tabella.
Se volessimo prendere il valore minino basterebbe invertire la condizione di confronto fra i due valori:
select key, valore
from [tabella] t1
where
( select count(*)
from [tabella] as t2
where t1.key = t2.key
and t2.valore < t1.valore -- prendo il minore
) = 0
Se vogliamo prendere i 2 valori più alti di ogni gruppo, basta cambiare la condizione finale:
select key, valore
from [tabella] t1
where
( select count(*)
from [tabella] as t2
where t1.key = t2.key
and t2.valore minore t1.valore
) < 2 -- numero di record restituiti per la condizione
Leave a Reply