SQL Server 2005 a introduit les fonctions de classement (ROW_NUMBER, RANK, DENSERANK et NTILE). Ces fonctions de classement s’appuient notamment sur la clause OVER, utilisée pour la notion de « fenêtrage ». Mais saviez-vous que cette clause est aussi utilisable avec les simples agrégats ?
Tout d’abord, un petit tour des fonctions de classement.
ROW_NUMBER permet de numéroter les enregistrements, sans notion d’ex-æquo. Par exemple, pour classer les livres des différentes bibliothèques d’une ville par ordre d’entrée au catalogue :

SELECT
	Titre,
	Auteur,
	ROW_NUMBER() OVER (PARTITION BY IdBibliotheque ORDER BY DateHeureEnregistrement)
FROM Livres

RANK permet de classer des enregistrements en tenant compte de la notion d’ex-æquo, avec donc potentiellement des trous dans la numérotation. Par exemple, pour les meilleurs joueurs d’un tournoi, classés par catégorie :

SELECT
	Nom,
	Prenom,
	RANK() OVER (PARTITION BY Categorie ORDER BY Score)
FROM Participants

DENSE_RANK, tout comme RANK, classe en tenant compte de la notion d’ex-æquo, mais cette fois-ci sans trou dans la numérotation.
Enfin, NTILE permet de découper un groupe en N parties égales (autant que faire se peut). Par exemple, pour déterminer quatre groupes de niveau pour les filles et autant pour les garçons pour une épreuve, on peut utiliser :

SELECT
	Nom,
	Prenom,
	NTILE(4) OVER (PARTITION BY Sexe ORDER BY Score)
FROM Participants

L’aide en ligne (http://msdn.microsoft.com/fr-fr/library/ms189798.aspx) vous apportera toutes les informations que vous désirez sur ce sujet.
Intéressons-nous maintenant de plus près à cette clause ORDER. Nous l’avons vue avec les fonctions de classement utilisée (ou en tout cas utilisable) sous sa forme complète, avec PARTITION BY facultatif et ORDER BY obligatoire. Mais il existe une autre syntaxe, dans laquelle ORDER BY n’est tout simplement pas présent, et PARTITION BY est au contraire sinon obligatoire du point de vue de la syntaxe pure, du moins vivement recommandé ; il s’agit des agrégats.
L’idée est de pouvoir appliquer l’agrégat sur des sous parties de l’ensemble des enregistrements, tout comme cela se ferait avec la clause GROUP BY. La différence avec GROUP BY réside dans le fait que cette dernière oblige chaque colonne retournée soit à faire partie de la clé de regroupement, soit à être le résultat d’une fonction d’agrégation.
Par exemple, pour obtenir les clients de différentes agences bancaires avec la part que représente le solde de leur compte par rapport à l’ensemble des clients de l’agence, on peut dans un premier temps calculer la somme des soldes pour chaque agence, puis diviser chaque solde de client par la somme correspondant à son agence :

WITH SommeAgence (IdAgence, Somme) AS
	(SELECT IdAgence, SUM(Solde)
		FROM Clients
		GROUP BY IdAgence)
SELECT
	c.Nom,
	c.Prenom,
	c.Solde,
	100.*c.Solde/sa.Somme
FROM Clients c
	join SommeAgence sa on sa.IdAgence=c.IdAgence

Mais grâce à la clause OVER qui peut être utilisée avec les fonctions d’agrégations, la syntaxe est nettement réduite, et surtout la requête est optimisée.

SELECT
	c.Nom,
	c.Prenom,
	c.Solde,
	100.*c.Solde/SUM(c.Solde) OVER (PARTITION BY IdAgence)
FROM Clients c

Personnellement, j’avoue que j’apprécie cette syntaxe. Libre à chacun d’en penser ce qu’il souhaite et de l’adapter à ses propres cas particuliers…