SQL server pb de Top 10 par catégorie

**henoch** · 20/01/2011, 10h05

Bonjour à tous,

Je n'arrive pas à résoudre mon problème c'est pour cela que je fais appel à vous. Je suis sur du SQL server et j’essaie de faire un classement sur les 10 meilleurs articles en CA (Montant_Net_Promo) par catégorie de produit. Peut être que la fonction Top ne correspond pas à ce que je dois faire. Mon code est celui ci et les résultats sont totalement incohérents :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 1 
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 2
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 3
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 4
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 6
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 7
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 10
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 11
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 12
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 15
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 21
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 31
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 50
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 51
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 52
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 53
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 56
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 57
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 59
order by [MONTANT_NET_PROMO] DESC ;

Merci pour votre aide .

**zinzineti** · 20/01/2011, 10h23

Mets le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORDER BY [MONTANT_NET_PROMO] DESC

pour chacune des requêtes séparément !

**henoch** · 20/01/2011, 10h37

Envoyé par zinzineti

Mets le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ORDER BY [MONTANT_NET_PROMO] DESC

pour chacune des requêtes séparément !

J'ai fait les modifs dans le code, il renvoi une erreur de "syntaxe sur le mot clé "union" . Mon code modifié est le suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 1 
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 2
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 3
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 4
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
order by [MONTANT_NET_PROMO] DESC
where GT = 6
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 7
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 10
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 11
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 12
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 15
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 21
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 31
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 50
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 51
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 52
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 53
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 56
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 57
order by [MONTANT_NET_PROMO] DESC
union all
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 59
order by [MONTANT_NET_PROMO] DESC ;

Merci pour la réponse rapide en tout cas ...

**zinzineti** · 20/01/2011, 10h54

Non tu ne peux pas faire des ORDER BY avec UNION.

C'est pourquoi j'ai dit séparément. donc pas avec des UNION

**henoch** · 20/01/2011, 11h02

Envoyé par zinzineti

Non tu ne peux pas faire des ORDER BY avec UNION.

C'est pourquoi j'ai dit séparément. donc pas avec des UNION

Ok, mais quelle solution adopter pour faire mon top 10 par catégorie, si j'enlève les union ma clause where est applicable que sur un GT et donc j'obtiens seulement 10 lignes. N'y a t-il pas une solution grâce à une boucle et une variable ou en utilisant les limite.

Merci de ton aide en tout cas .

**zinzineti** · 20/01/2011, 11h22

Essaye ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 1 
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 2
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 3
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 4
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
order by [MONTANT_NET_PROMO] DESC
where GT = 6
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 7
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 10
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 11
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 12
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 15
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 21
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 31
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 50
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 51
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 52
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 53
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 56
order by [MONTANT_NET_PROMO] DESC
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 57
order by [MONTANT_NET_PROMO] DESC
 
 
SELECT 
TOP 10 [GT],[CODE_ARTICLE],[LIB_ARTICLE],[DOSSIER],[CNUF],[LIB_FOURN],[MONTANT_NET_PROMO]
FROM [dbo].[Regul_par_article]
where GT = 59
order by [MONTANT_NET_PROMO] DESC ;

SQL server pb de Top 10 par catégorie

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager