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 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226
| DECLARE @t TABLE
(
Magasin VARCHAR(20),
Srv INT,
Prod INT,
Qte INT,
id INT
);
INSERT @t VALUES ('MMGEN',2920,11980,44,1);
INSERT @t VALUES ('MMGEN',5240,14774,21000,2);
INSERT @t VALUES ('MMGEN',2201,11980,151,3);
INSERT @t VALUES ('MMGEN',2201,14774,2250,4);
INSERT @t VALUES ('MMGEN',2002,14774,1500,5);
INSERT @t VALUES ('MMGEN',5005,15529,1,6);
INSERT @t VALUES ('MMGEN',3102,42011,24,7);
INSERT @t VALUES ('MMGEN',3120,42011,2,8);
INSERT @t VALUES ('MMGEN',2811,11980,60,9);
INSERT @t VALUES ('MMGEN',3751,15529,12,10);
INSERT @t VALUES ('MMGEN',3200,15529,7,11);
INSERT @t VALUES ('MMGEN',5003,15529,6,12);
INSERT @t VALUES ('MMGEN',2860,15529,7,13);
INSERT @t VALUES ('MMGEN',5501,42011,3,14);
INSERT @t VALUES ('MMGEN',3049,14774,2600,15);
INSERT @t VALUES ('MMGEN',5301,15529,102,16);
INSERT @t VALUES ('MMGEN',3049,11980,126,17);
INSERT @t VALUES ('MMGEN',3112,15529,7,18);
INSERT @t VALUES ('MMGEN',5006,15529,3,19);
INSERT @t VALUES ('MMGEN',5060,14774,200,20);
INSERT @t VALUES ('MMGEN',2300,14774,1900,21);
INSERT @t VALUES ('MMGEN',8002,15529,14,22);
INSERT @t VALUES ('MMGEN',3112,42011,10,23);
INSERT @t VALUES ('MMGEN',2900,14774,250,24);
INSERT @t VALUES ('MMGEN',2202,11980,10,25);
INSERT @t VALUES ('MMGEN',3013,15132,35,26);
INSERT @t VALUES ('MMGEN',5201,15529,35,27);
INSERT @t VALUES ('MMGEN',5006,42011,4,28);
INSERT @t VALUES ('MMGEN',5106,42011,25,29);
INSERT @t VALUES ('MMGEN',2300,11980,27,30);
INSERT @t VALUES ('MMGEN',2001,15529,10,31);
INSERT @t VALUES ('MMGEN',3102,15529,5,32);
INSERT @t VALUES ('MMGEN',3040,14774,8225,33);
INSERT @t VALUES ('MMGEN',6210,15529,23,34);
INSERT @t VALUES ('MMGEN',8002,42011,3,35);
INSERT @t VALUES ('MMGEN',5240,11980,295,36);
INSERT @t VALUES ('MMGEN',2201,15529,26,37);
INSERT @t VALUES ('MMGEN',2113,11980,240,38);
INSERT @t VALUES ('MMGEN',2113,14774,5000,39);
INSERT @t VALUES ('MMGEN',3030,11980,237,40);
INSERT @t VALUES ('MMGEN',3013,11980,668,41);
INSERT @t VALUES ('MMGEN',2811,14774,625,42);
INSERT @t VALUES ('MMGEN',5240,15529,10,43);
INSERT @t VALUES ('MMGEN',5124,11980,48,44);
INSERT @t VALUES ('MMGEN',5060,15529,22,45);
INSERT @t VALUES ('MMGEN',5302,14774,350,46);
INSERT @t VALUES ('MMGEN',3012,42011,8,47);
INSERT @t VALUES ('MMGEN',2871,15529,4,48);
INSERT @t VALUES ('MMGEN',3012,15529,1,49);
INSERT @t VALUES ('MMGEN',2600,15529,4,50);
INSERT @t VALUES ('MMGEN',2001,11980,28,51);
INSERT @t VALUES ('MMGEN',2001,14774,675,52);
INSERT @t VALUES ('MMGEN',5128,15529,8,53);
INSERT @t VALUES ('MMGEN',3120,11980,129,54);
INSERT @t VALUES ('MMGEN',3120,14774,4050,55);
INSERT @t VALUES ('MMGEN',2880,15529,6,56);
INSERT @t VALUES ('MMGEN',5001,15529,8,57);
INSERT @t VALUES ('MMGEN',5030,15529,2,58);
INSERT @t VALUES ('MMGEN',2910,14774,400,59);
INSERT @t VALUES ('MMGEN',2880,14774,100,60);
INSERT @t VALUES ('MMGEN',2111,15529,5,61);
INSERT @t VALUES ('MMGEN',5213,14774,600,62);
INSERT @t VALUES ('MMGEN',2111,14774,950,63);
INSERT @t VALUES ('MMGEN',2111,11980,96,64);
INSERT @t VALUES ('MMGEN',3000,42011,4,65);
INSERT @t VALUES ('MMGEN',2001,42011,10,66);
INSERT @t VALUES ('MMGEN',3020,14774,7800,67);
INSERT @t VALUES ('MMGEN',3200,14774,13970,68);
INSERT @t VALUES ('MMGEN',3112,11980,1261,69);
INSERT @t VALUES ('MMGEN',2815,15529,4,70);
INSERT @t VALUES ('MMGEN',2815,42011,6,71);
INSERT @t VALUES ('MMGEN',5201,42011,10,72);
INSERT @t VALUES ('MMGEN',5301,42011,18,73);
INSERT @t VALUES ('MMGEN',6210,11980,171,74);
INSERT @t VALUES ('MMGEN',5061,15529,4,75);
INSERT @t VALUES ('MMGEN',5240,42011,9,76);
INSERT @t VALUES ('MMGEN',6220,42011,1,77);
INSERT @t VALUES ('MMGEN',6210,14774,100,78);
INSERT @t VALUES ('MMGEN',5214,15529,21,79);
INSERT @t VALUES ('MMGEN',5126,15529,20,80);
INSERT @t VALUES ('MMGEN',6210,42011,2,81);
INSERT @t VALUES ('MMGEN',2401,15529,10,82);
INSERT @t VALUES ('MMGEN',2700,11980,5,83);
INSERT @t VALUES ('MMGEN',3600,15529,11,84);
INSERT @t VALUES ('MMGEN',3040,11980,633,85);
INSERT @t VALUES ('MMGEN',3752,15529,13,86);
INSERT @t VALUES ('MMGEN',2002,15529,1,87);
INSERT @t VALUES ('MMGEN',2202,15529,4,88);
INSERT @t VALUES ('MMGEN',5213,11980,192,89);
INSERT @t VALUES ('MMGEN',2860,42011,2,90);
INSERT @t VALUES ('MMGEN',2850,42011,4,91);
INSERT @t VALUES ('MMGEN',3025,42011,3,92);
INSERT @t VALUES ('MMGEN',3010,11980,96,93);
INSERT @t VALUES ('MMGEN',3010,14774,3950,94);
INSERT @t VALUES ('MMGEN',3030,14774,6950,95);
INSERT @t VALUES ('MMGEN',6201,15529,40,96);
INSERT @t VALUES ('MMGEN',6201,42011,2,97);
INSERT @t VALUES ('MMGEN',3600,42011,2,98);
INSERT @t VALUES ('MMGEN',2113,15529,12,99);
INSERT @t VALUES ('MMGEN',2880,11980,6,100);
INSERT @t VALUES ('MMGEN',2880,15132,15,101);
INSERT @t VALUES ('MMGEN',2910,11980,8,102);
INSERT @t VALUES ('MMGEN',3752,11980,1359,103);
INSERT @t VALUES ('MMGEN',3200,11980,639,104);
INSERT @t VALUES ('MMGEN',2202,14774,250,105);
INSERT @t VALUES ('MMGEN',5124,14774,2000,106);
INSERT @t VALUES ('MMGEN',2002,11980,27,107);
INSERT @t VALUES ('MMGEN',5106,15529,28,108);
INSERT @t VALUES ('MMGEN',2300,15529,17,109);
INSERT @t VALUES ('MMGEN',5401,15529,6,110);
INSERT @t VALUES ('MMGEN',5503,15529,4,111);
INSERT @t VALUES ('MMGEN',7001,14774,400,112);
INSERT @t VALUES ('MMGEN',3026,42011,1,113);
INSERT @t VALUES ('MMGEN',3010,42011,1,114);
INSERT @t VALUES ('MMGEN',5129,15529,2,115);
INSERT @t VALUES ('MMGEN',2821,15132,1,116);
INSERT @t VALUES ('MMGEN',3026,15529,5,117);
INSERT @t VALUES ('MMGEN',3200,15132,1,118);
INSERT @t VALUES ('MMGEN',2890,15529,2,119);
INSERT @t VALUES ('MMGEN',5331,11980,10,120);
INSERT @t VALUES ('MMGEN',3026,15132,13,121);
INSERT @t VALUES ('MMGEN',5125,15529,20,122);
INSERT @t VALUES ('MMGEN',3013,14774,9225,123);
INSERT @t VALUES ('MMGEN',3024,14774,9525,124);
INSERT @t VALUES ('MMGEN',2861,42011,6,125);
INSERT @t VALUES ('MMGEN',2861,15529,2,126);
INSERT @t VALUES ('MMGEN',5030,42011,1,127);
INSERT @t VALUES ('MMGEN',5403,15529,1,128);
INSERT @t VALUES ('MMGEN',3600,14774,39196,129);
INSERT @t VALUES ('MMGEN',3040,15529,5,130);
INSERT @t VALUES ('MMGEN',2821,15529,1,131);
INSERT @t VALUES ('MMGEN',8102,42011,2,132);
INSERT @t VALUES ('MMGEN',2920,14774,250,133);
INSERT @t VALUES ('MMGEN',3820,14774,27450,134);
INSERT @t VALUES ('MMGEN',3752,14774,23600,135);
INSERT @t VALUES ('MMGEN',2811,15529,5,136);
INSERT @t VALUES ('MMGEN',3024,15529,3,137);
INSERT @t VALUES ('MMGEN',5220,11980,24,138);
INSERT @t VALUES ('MMGEN',3020,15529,3,139);
INSERT @t VALUES ('MMGEN',3031,42011,1,140);
INSERT @t VALUES ('MMGEN',5241,14774,4900,141);
INSERT @t VALUES ('MMGEN',2900,42011,3,142);
INSERT @t VALUES ('MMGEN',2002,42011,2,143);
INSERT @t VALUES ('MMGEN',7001,11980,10,144);
INSERT @t VALUES ('MMGEN',5302,15529,6,145);
INSERT @t VALUES ('MMGEN',2890,42011,2,146);
INSERT @t VALUES ('MMGEN',5302,42011,2,147);
INSERT @t VALUES ('MMGEN',6202,15529,2,148);
INSERT @t VALUES ('MMGEN',7001,42011,2,149);
INSERT @t VALUES ('MMGEN',2700,15529,3,150);
INSERT @t VALUES ('MMGEN',6220,15529,4,151);
INSERT @t VALUES ('MMGEN',3820,42011,2,152);
INSERT @t VALUES ('MMGEN',5401,42011,1,153);
INSERT @t VALUES ('MMGEN',3026,14774,6450,154);
INSERT @t VALUES ('MMGEN',3302,14774,2025,155);
INSERT @t VALUES ('MMGEN',3020,11980,222,156);
INSERT @t VALUES ('MMGEN',3026,11980,273,157);
INSERT @t VALUES ('MMGEN',3012,14774,6250,158);
INSERT @t VALUES ('MMGEN',3102,14774,10300,159);
INSERT @t VALUES ('MMGEN',3000,11980,10,160);
INSERT @t VALUES ('MMGEN',3000,14774,2250,161);
INSERT @t VALUES ('MMGEN',3600,15132,38,162);
INSERT @t VALUES ('MMGEN',3600,11980,1127,163);
INSERT @t VALUES ('MMGEN',5241,11980,389,164);
INSERT @t VALUES ('MMGEN',3302,15132,34,165);
INSERT @t VALUES ('MMGEN',3302,11980,799,166);
INSERT @t VALUES ('MMGEN',3024,15132,20,167);
INSERT @t VALUES ('MMGEN',3024,11980,789,168);
INSERT @t VALUES ('MMGEN',3112,15132,25,169);
INSERT @t VALUES ('MMGEN',3010,15132,52,170);
INSERT @t VALUES ('MMGEN',3102,15132,9,171);
INSERT @t VALUES ('MMGEN',3102,11980,408,172);
INSERT @t VALUES ('MMGEN',3030,15132,40,173);
INSERT @t VALUES ('MMGEN',3820,15132,20,174);
INSERT @t VALUES ('MMGEN',2401,11980,725,175);
INSERT @t VALUES ('MMGEN',2401,14774,9600,176);
INSERT @t VALUES ('MMGEN',3112,14774,11150,177);
INSERT @t VALUES ('MMGEN',3012,11980,443,178);
INSERT @t VALUES ('MMGEN',2401,15132,31,179);
INSERT @t VALUES ('MMGEN',3012,15132,27,180);
INSERT @t VALUES ('MMGEN',3820,11980,730,181);
INSERT @t VALUES ('MMGEN',3752,15132,21,182);
INSERT @t VALUES ('MMGEN',3000,15132,2,183);
INSERT @t VALUES ('MMGEN',3020,15132,18,184);
-- 1ere solution
WITH RANK_MAG
AS
(
SELECT
DENSE_RANK() OVER(PARTITION BY Magasin, Prod ORDER BY Qte DESC) AS num,
Magasin,
Prod,
Qte
FROM @t
)
SELECT
Magasin,
Prod,
Qte
FROM RANK_MAG
WHERE num <= 5
-- 2eme solution
SELECT DISTINCT
T.Magasin,
T.Prod,
T_RANK.qte
FROM @t AS T
CROSS APPLY
(
SELECT TOP(5) WITH TIES Qte
FROM @t AS T2
WHERE T.Prod = T2.Prod
ORDER BY Qte DESC
) AS T_RANK
ORDER BY T.Magasin, T.Prod, T_RANK.Qte DESC |
Partager