PC Easy Weblog

mars 3, 2009

SQL Server – ROW_NUMBER(), l’équivalent du ROWNUM d’Oracle

Filed under: Développement — Étiquettes : — trucmuche92 @ 8:54

Avec SQL Server 2005, on cette nouvelle fonction qui permet de numéroté d’une façon distinct chaque ligne : ROW_NUMBER()

Prenons par exemple le contenu de la table suivante :

select codex, commune from villes
codex commune
----- --------------------
02300 ABBECOURT
20243 ABBAZIA
25320 ABBANS DESSOUS
25440 ABBANS DESSUS
44170 ABBARETZ
54610 ABAUCOURT
55130 ABAINVILLE
55400 ABAUCOURT HAUTECOURT
59268 ABANCOURT
60220 ABANCOURT
60430 ABBECOURT
64460 AAST

Il s’agit donc de numéroter chaque ligne, mais ROW_NUMBER() nécessite de préciser sur quelle champ on défini l’ordre de numérotation.
Aussi la syntaxe exacte pour le champ qui reçoit la numérotation est  :

ROW_NUMBER() over(order by champ)

Avec le jeu de données ci-dessus, on pourra numéroter les lignes par ordre croissant de commune :

select codex, commune, row_number() over(order by commune) as num
from villes 
codex commune              num
----- -------------------- --------------------
64460 AAST                 1
55130 ABAINVILLE           2
59268 ABANCOURT            3
60220 ABANCOURT            4
54610 ABAUCOURT            5
55400 ABAUCOURT HAUTECOURT 6
25320 ABBANS DESSOUS       7
25440 ABBANS DESSUS        8
44170 ABBARETZ             9
20243 ABBAZIA              10
02300 ABBECOURT            11
60430 ABBECOURT            12

On remarquera la fonction ROW_NUMBER() filera toujours un numéro distinct, même si le champ sur lequel est poser la numérotation est le même d’une ligne à l’autre (commune ABANCOURT), il s’agit donc bien d’un numéro de ligne.

On pourra conserver la numérotation ci dessus, mais en présentant les données ordonnées par code postale

select codex, commune, row_number() over(order by commune) as num
from villes order by codex
codex commune              num
----- -------------------- --------------------
02300 ABBECOURT            11
20243 ABBAZIA              10
25320 ABBANS DESSOUS       7
25440 ABBANS DESSUS        8
44170 ABBARETZ             9
54610 ABAUCOURT            5
55130 ABAINVILLE           2
55400 ABAUCOURT HAUTECOURT 6
59268 ABANCOURT            3
60220 ABANCOURT            4
60430 ABBECOURT            12
64460 AAST                 1

Ca c’est interressant !

Pour aller plus loin :

– on peut compléter la partie order by par des champs complémentaires :

select codex, dpt, commune,
row_number() over(order by Dpt asc, commune desc) as num
from villes

– ROW_NUMBER() a aussi son petit frère RANK() qui effectue un classement :

select codex, commune, rank() over(order by commune) as num
from villes order by commune
codex commune              num
----- -------------------- --------------------
64460 AAST                 1
55130 ABAINVILLE           2
59268 ABANCOURT            3
60220 ABANCOURT            3
54610 ABAUCOURT            5
55400 ABAUCOURT HAUTECOURT 6
25320 ABBANS DESSOUS       7
25440 ABBANS DESSUS        8
44170 ABBARETZ             9
20243 ABBAZIA              10
02300 ABBECOURT            11
60430 ABBECOURT            11

Créez un site Web ou un blog gratuitement sur WordPress.com.