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