Retourner le rang des résultats sous SQLServer 2000

Version imprimable

Bonjour,

J'ai un jeu de données équivalent à cela :

Code:

1
2
3
4
5
6
7
8
9
10
 
Numero_Compte         Groupe          Montant
1001                         A                 1000
1002                         B                 2300
1003                         A                 1500
1004                         C                 1000
1005                         A                 1200
1006                         C                 1500
1007                         C                 2000
1008                         B                 1400

et je cherche à donner le rang de chaque compte au sein de chaque groupe (le plus grand montant correspondant au rang 1)

soit :

Code:

1
2
3
4
5
6
7
8
9
10
 
Numero_Compte         Groupe          Montant          Rang_Groupe
1003                         A                 1500              1
1005                         A                 1200              2
1001                         A                 1000              3
1002                         B                 2300              1
1008                         B                 1400              2
1007                         C                 2000              1
1006                         C                 1500              2
1004                         C                 1000              3

Quelqu'un aurait-il une idée de la manière dont il faut s'y prendre ?
Merci d'avance

Bonjour,

Je ne suis pas experte, mais je ferai un truc comme ça :
Code:

1 2 3 4 5 SELECT Numero_Compte, Groupe, Montant, ROW_NUMBER() OVER(PARTITION BY Groupe ORDER BY Montant) AS Rang_Groupe FROM maTable
Cordialement,

Arkhena

Il n'y a pas de fonctions analytiques avec SQL Server 2000, mais on voit bien l'utilité d'upgrader ses bases de données car effectivement la requête est nettement plus simple avec.
Attention au sens du tri, il faudrait faire un "ORDER BY Montant DESC" dans ce cas.

Voici deux solutions.
Avec une requête scalaire :
Code:

1 2 3 4 5 6 7 select MT1.Numero_Compte, MT1.Groupe, MT1.Montant, (select count(*) from MaTable AS MT2 where MT2.Groupe = MT1.Groupe and MT2.Montant >= MT1.Montant) as Rang_Groupe from MaTable AS MT1 order by MT1.Groupe ASC, Rang_Groupe ASC;
Avec une jointure (je préfère cette solution) :
Code:

1 2 3 4 5 6 7 8 9 select MT1.Numero_Compte, MT1.Groupe, MT1.Montant, count(*) AS Rang_Groupe from MaTable AS MT1 INNER JOIN MaTable AS MT2 ON MT2.Groupe = MT1.Groupe and MT2.Montant >= MT1.Montant group by MT1.Numero_Compte, MT1.Groupe, MT1.Montant order by MT1.Groupe ASC, Rang_Groupe ASC

arf, oui, je suis bien tombé sur les fonctions analytiques en cherchant un peu mais malheureusement, je n'ai pas la bonne version de SQLServer (et ce n'est pas moi qui prend la décision d'upgrader...)

j'avais essayé la scalaire avant de poser la question, et j'avais eu des résultats bizarres, je pensais que j'avais faux :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Numero_Compte Groupe Montant Rang 34193 A 4661 1 44894 A 6268 1 39048 A 4222 3 45048 A 4094 4 ... 43440 AP 341 17 54739 ASC 2252 2 61045 ASC 2252 2 84931 ASC 2154 3 61142 ASC 1587 4
Avec la jointure, je n'ai pas le même résultat mais en regardant dans le détail il y a encore des choses qui clochent :
Code:

1 2 3 4 Numero_Compte Groupe Montant Rang 81602 S 976 12 82012 S 987 12
Une idée de quoi ça pourrait provenir ?

03/11/2009, 16h17
Waldar

Oui vous n'avez pas précisé comment vous souhaitez gérer les égalités (le cas dans le groupe ASC).
Par contre sur le groupe A, c'est peut-être du un problème d'espace ou de caractère invisible.

Pour le groupe S, effectivement c'est étrange.

Pouvez-vous fournir un jeu de données sur vos données réelle sur un groupe ou deux qui pose problème (une vingtaine de lignes par groupe maximum par contre) ?

Peut-être aurais-je dû préciser que je travaille à partir d'une autre query (pour faire du sum par numéro de compte), je ne sais pas si cette information est pertinente

les données (j'avais tronqué les décimales pour une meilleure lisibilité) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
Compte   Group	  Montant
23441	A	755,95656
28972	A	1293,1123
28973	A	388,515569
32091	A	2875,866
34193	A	4661,968
39048	A	4222,322
40082	A	1704,2694
42908	A	3759,192
43440	A	341,1185
43523	A	2325,3192
44288	A	499,47
44600	A	1839,1524
44894	A	6268,168
45048	A	4094,229
45246	A	1964,252
46530	A	626,9996
47225	A	2502,353
47368	A	1150,895
37237	S	312,123
39156	S	309,65827
45230	S	615,719
45841	S	365,9673
46531	S	576,6831
47181	S	1450,2132
48753	S	79,2658
49553	S	923,2749
50759	S	138,9952
50760	S	169,6536
52001	S	314,1073
52557	S	106,76316
52761	S	469,9118
53126	S	350,267
54071	S	591,9545
54411	S	444,4285
54739	S	2252,167
55371	S	79,9059
55527	S	677,3717
55923	S	815,6583
57748	S	740,1626

Merci en tout cas

03/11/2009, 16h41
Wisefool

ah oui, et il n'y a pas de bug quand je demande le rang de chaque compte sur l'ensemble et non par groupe
03/11/2009, 16h57
Wisefool

1 pièce(s) jointe(s)

je viens de découvrir la possibilité de pièce jointe, ça sera peut-être plus pratique

j'ai du coup mis les données initiales (2 groupes qui posent problème) et le résultat obtenu avec la jointure

SI vous n'avez pas de fonction de fenêtrage (apparues avec 2005), il faut utiliser la technique du Row Value Cnstructor comme je l'ai indiqué ici : http://sqlpro.developpez.com/cours/sqlaz/select/#L8

Avec vos données ; (merci de les mettre sous forme d'ordre SQL INSERT comme indiqué dans la charte ici http://www.developpez.net/forums/d96...vement-poster/)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
CREATE TABLE T (Numero_Compte  INT, Groupe CHAR(1), Montant DECIMAL(16,4));
 
INSERT INTO T VALUES (23441, 'A', 755.95655999999997)
INSERT INTO T VALUES (28972, 'A', 1293.1123)
INSERT INTO T VALUES (28973, 'A', 388.51556899999997)
INSERT INTO T VALUES (32091, 'A', 2875.866)
INSERT INTO T VALUES (34193, 'A', 4661.9679999999998)
INSERT INTO T VALUES (39048, 'A', 4222.3220000000001)
INSERT INTO T VALUES (40082, 'A', 1704.2694000000001)
INSERT INTO T VALUES (42908, 'A', 3759.192)
INSERT INTO T VALUES (43440, 'A', 341.11849999999998)
INSERT INTO T VALUES (43523, 'A', 2325.3191999999999)
INSERT INTO T VALUES (44288, 'A', 499.46999999999997)
INSERT INTO T VALUES (44600, 'A', 1839.1524000000002)
INSERT INTO T VALUES (44894, 'A', 6268.1680000000006)
INSERT INTO T VALUES (45048, 'A', 4094.2290000000003)
INSERT INTO T VALUES (45246, 'A', 1964.252)
INSERT INTO T VALUES (46530, 'A', 626.99959999999999)
INSERT INTO T VALUES (47225, 'A', 2502.3530000000001)
INSERT INTO T VALUES (47368, 'A', 1150.8950000000002)
INSERT INTO T VALUES (37237, 'S', 312.12299999999999)
INSERT INTO T VALUES (39156, 'S', 309.65827000000002)
INSERT INTO T VALUES (45230, 'S', 615.71899999999994)
INSERT INTO T VALUES (45841, 'S', 365.96730000000002)
INSERT INTO T VALUES (46531, 'S', 576.68309999999997)
INSERT INTO T VALUES (47181, 'S', 1450.2131999999999)
INSERT INTO T VALUES (48753, 'S', 79.265800000000013)
INSERT INTO T VALUES (49553, 'S', 923.27489999999989)
INSERT INTO T VALUES (50759, 'S', 138.99520000000001)
INSERT INTO T VALUES (50760, 'S', 169.65360000000001)
INSERT INTO T VALUES (52001, 'S', 314.10730000000001)
INSERT INTO T VALUES (52557, 'S', 106.76316)
INSERT INTO T VALUES (52761, 'S', 469.91180000000008)
INSERT INTO T VALUES (53126, 'S', 350.267)
INSERT INTO T VALUES (54071, 'S', 591.95449999999994)
INSERT INTO T VALUES (54411, 'S', 444.42850000000004)
INSERT INTO T VALUES (54739, 'S', 2252.1669999999999)
INSERT INTO T VALUES (55371, 'S', 79.905900000000003)
INSERT INTO T VALUES (55527, 'S', 677.37170000000003)
INSERT INTO T VALUES (55923, 'S', 815.65829999999994)
INSERT INTO T VALUES (57748, 'S', 740.16260000000011)
INSERT INTO T VALUES (57776, 'S', 60.274760000000001)
INSERT INTO T VALUES (58027, 'S', 290.85230000000001)
INSERT INTO T VALUES (58467, 'S', 473.30310000000003)
INSERT INTO T VALUES (59912, 'S', 293.56253000000004)
INSERT INTO T VALUES (60131, 'S', 935.30869999999982)
INSERT INTO T VALUES (60471, 'S', 1164.7419999999997)
INSERT INTO T VALUES (60502, 'S', 924.15800000000002)
INSERT INTO T VALUES (61045, 'S', 2252.1669999999999)
INSERT INTO T VALUES (61142, 'S', 1587.1517000000001)
INSERT INTO T VALUES (62138, 'S', 1222.33115)
INSERT INTO T VALUES (62141, 'S', 916.33240000000001)
INSERT INTO T VALUES (63508, 'S', 494.31239999999997)
INSERT INTO T VALUES (63819, 'S', 1443.655)
INSERT INTO T VALUES (63820, 'S', 254.24241999999998)
INSERT INTO T VALUES (64178, 'S', 425.76859999999999)
INSERT INTO T VALUES (64366, 'S', 692.41409999999996)
INSERT INTO T VALUES (68239, 'S', 133.77609999999999)
INSERT INTO T VALUES (70239, 'S', 785.91809999999998)
INSERT INTO T VALUES (71499, 'S', 395.78149999999999)
INSERT INTO T VALUES (73766, 'S', 1009.0872999999999)
INSERT INTO T VALUES (75140, 'S', 86.653109999999998)
INSERT INTO T VALUES (76821, 'S', 331.56970000000001)
INSERT INTO T VALUES (80419, 'S', 1035.0203999999999)
INSERT INTO T VALUES (81602, 'S', 976.21900000000005)
INSERT INTO T VALUES (82012, 'S', 987.50409999999999)
INSERT INTO T VALUES (82209, 'S', 212.70783999999998)
INSERT INTO T VALUES (82212, 'S', 379.53329999999994)
INSERT INTO T VALUES (82311, 'S', 1334.7745)
INSERT INTO T VALUES (84846, 'S', 167.77190000000002)
INSERT INTO T VALUES (84927, 'S', 360.66030000000001)
INSERT INTO T VALUES (84931, 'S', 2154.904)
INSERT INTO T VALUES (85116, 'S', 106.4751)

Voici la requête :

Code:

1
2
3
4
5
6
7
8
SELECT T1.*, COUNT(*)
FROM   T AS T1
       INNER JOIN T AS T2
             ON (T1.Numero_Compte < T2.Numero_Compte
                OR (T1.Numero_Compte = T2.Numero_Compte AND T1.Montant <= T2.Montant))
                AND T1.Groupe = T2.Groupe
GROUP  BY T1.Numero_Compte, T1. Groupe, T1.Montant 
ORDER BY Groupe, 4

Dont le résultat est :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
Numero_Compte Groupe Montant                                 
------------- ------ --------------------------------------- -----------
47368         A      1150.8950                               1
47225         A      2502.3530                               2
46530         A      626.9996                                3
45246         A      1964.2520                               4
45048         A      4094.2290                               5
44894         A      6268.1680                               6
44600         A      1839.1524                               7
44288         A      499.4700                                8
43523         A      2325.3192                               9
43440         A      341.1185                                10
42908         A      3759.1920                               11
40082         A      1704.2694                               12
39048         A      4222.3220                               13
34193         A      4661.9680                               14
32091         A      2875.8660                               15
28973         A      388.5156                                16
28972         A      1293.1123                               17
23441         A      755.9566                                18
85116         S      106.4751                                1
84931         S      2154.9040                               2
84927         S      360.6603                                3
84846         S      167.7719                                4
82311         S      1334.7745                               5
82212         S      379.5333                                6
82209         S      212.7078                                7
82012         S      987.5041                                8
81602         S      976.2190                                9
80419         S      1035.0204                               10
76821         S      331.5697                                11
75140         S      86.6531                                 12
73766         S      1009.0873                               13
71499         S      395.7815                                14
70239         S      785.9181                                15
68239         S      133.7761                                16
64366         S      692.4141                                17
64178         S      425.7686                                18
63820         S      254.2424                                19
63819         S      1443.6550                               20
63508         S      494.3124                                21
62141         S      916.3324                                22
62138         S      1222.3312                               23
61142         S      1587.1517                               24
61045         S      2252.1670                               25
60502         S      924.1580                                26
60471         S      1164.7420                               27
60131         S      935.3087                                28
59912         S      293.5625                                29
58467         S      473.3031                                30
58027         S      290.8523                                31
57776         S      60.2748                                 32
57748         S      740.1626                                33
55923         S      815.6583                                34
55527         S      677.3717                                35
55371         S      79.9059                                 36
54739         S      2252.1670                               37
54411         S      444.4285                                38
54071         S      591.9545                                39
53126         S      350.2670                                40
52761         S      469.9118                                41
52557         S      106.7632                                42
52001         S      314.1073                                43
50760         S      169.6536                                44
50759         S      138.9952                                45
49553         S      923.2749                                46
48753         S      79.2658                                 47
47181         S      1450.2132                               48
46531         S      576.6831                                49
45841         S      365.9673                                50
45230         S      615.7190                                51
39156         S      309.6583                                52
37237         S      312.1230                                53

Petite remarque : songez à migrer vers SQL Server 2008. SQL Server 2000 ne sera plus supporté ce qui peut signifier qu'il ne pourra fonctionner dans les OS futurs, ce qui compromet l'avenir de votre base et sa portabilité !

A +

Sur le groupe A, je vois ceci dans le fichier excel :
Code:

1 2 43523 A 2325.3191999999999 8 45246 A 1964.2520000000002 8
Or quand j'exécute la requête, j'obtiens de mon côté le bon résultat :
Code:

1 2 43523 A 2325.3191999999999 8 45246 A 1964.2520000000002 9
Concernant le groupe S, vous n'avez pas énoncé de règle de gestion concernant les égalités.

Que voulez-vous obtenir ?
Code:

1 2 3 54739 S 2252.1669999999999 1 61045 S 2252.1669999999999 1 84931 S 2154.904 3
Code:

1 2 3 54739 S 2252.1669999999999 1 61045 S 2252.1669999999999 1 84931 S 2154.904 2
Code:

1 2 3 54739 S 2252.1669999999999 1 61045 S 2252.1669999999999 2 84931 S 2154.904 3
Dans ce dernier cas, on trie alors par numéro de compte ?

Autre chose ?

03/11/2009, 18h12
Wisefool

Je prends bonne note pour le jeu de données, c'était dans la charte je n'aurais pas dû me le faire rappeler...

Je reprends à mon compte le conseil pour la migration, je vais faire remonter ce point aux décideurs :)

Enfin, un grand merci pour la requête, ça fonctionne nickel (une petite erreur sur la jointure mais j'ai compris le principe et j'ai le résultat souhaité)

Mes remerciements à Waldar et Arkhena également