Requete SQL

Différentes solutions :

CREATE TABLE T_HUIT_HUI
(PRS_ID INT,
MOI_ID INT,
HUI_MONTANT FLOAT)

INSERT INTO T_HUIT_HUI VALUES (1, 3, 123.45)
INSERT INTO T_HUIT_HUI VALUES (5, 5, 125.43)
INSERT INTO T_HUIT_HUI VALUES (18, 12, 526.28)
INSERT INTO T_HUIT_HUI VALUES (21, 1, 125.85)
INSERT INTO T_HUIT_HUI VALUES (43, 6, 985.42)
INSERT INTO T_HUIT_HUI VALUES (11, 4, 963.25)
INSERT INTO T_HUIT_HUI VALUES (22, 3, 987.45)
INSERT INTO T_HUIT_HUI VALUES (18, 5, 331.84)
INSERT INTO T_HUIT_HUI VALUES (21, 4, 175.85)
INSERT INTO T_HUIT_HUI VALUES (43, 8, 225.48)
INSERT INTO T_HUIT_HUI VALUES (11, 5, 993.25)
INSERT INTO T_HUIT_HUI VALUES (22, 1, 667.49)
INSERT INTO T_HUIT_HUI VALUES (67, 2, 331.84)
INSERT INTO T_HUIT_HUI VALUES (18, 1, 711.89)

-- premère solutation : non relationnelle :

SELECT TOP 8 *
FROM T_HUIT_HUI
ORDER BY HUI_MONTANT DESC

PRS_ID MOI_ID HUI_MONTANT
----------- ----------- --------------------
11 5 993.25
22 3 987.45000000000005
43 6 985.41999999999996
11 4 963.25
18 1 711.88999999999999
22 1 667.49000000000001
18 12 526.27999999999997
67 2 331.83999999999997

--en effet elle s'arrête ici alors qu'il y a un ex aequo !
-->
18 5 331.83999999999997
43 8 225.47999999999999
21 4 175.84999999999999
21 1 125.84999999999999
5 5 125.43000000000001
1 3 123.45

--seconde solution, relationnelle, mais qui intègre (ou non) les ex aequo :

SELECT T1.PRS_ID, T1.MOI_ID, COUNT(T2.PRS_ID)
FROM T_HUIT_HUI T1
LEFT OUTER JOIN T_HUIT_HUI T2
ON T1.HUI_MONTANT < T2.HUI_MONTANT
GROUP BY T1.PRS_ID, T1.MOI_ID
HAVING COUNT(T2.PRS_ID) < 8
ORDER BY 3

PRS_ID MOI_ID
----------- ----------- -----------
11 5 0
22 3 1
43 6 2
11 4 3
18 1 4
22 1 5
18 12 6
18 5 7
67 2 7

A +

--
Frédéric BROUARD - MVP SQL Server, expert SGBDR & SQL
Le site SQL et les SGBDR http://sqlpro.developpez.com
Audit, conseil, formation, modélisation, optimisation
=> http://www.datasapiens.com


"Amirallia" a écrit :

Bonjour,

J'aimerais extraire d'une table, comportant un mois, un idPersone et un
montant, soit les champs idMois, idPerson, Mnt, les 8 plus grands
montants de chaque personne parmi les 12 mois.

Donc ma requete devrait avoir au max 8 lignes par personne.

Une idée ?

SQLpro a écrit:

--en effet elle s'arrête ici alors qu'il y a un ex aequo !
-->

Bonjour,

Pour gérer le problème de l'ex aequo :

SELECT TOP 8 WITH TIES *
FROM T_HUIT_HUI
ORDER BY HUI_MONTANT DESC


--
Rudi Bruchez
Consultant indépendant
modélisation, administration, optimisation,
Solutions MS SQL Server et informatique libre.
MCDBA, SCJP2
http://www.babaluga.com/

Rudi Bruchez a exposé le 20.07.2006 :

SQLpro a écrit:

--en effet elle s'arrête ici alors qu'il y a un ex aequo !
-->

Bonjour,

Pour gérer le problème de l'ex aequo :

SELECT TOP 8 WITH TIES *
FROM T_HUIT_HUI
ORDER BY HUI_MONTANT DESC

Que signifie le WITH TIES ?

SQLpro a écrit :

Différentes solutions :

CREATE TABLE T_HUIT_HUI
(PRS_ID INT,
MOI_ID INT,
HUI_MONTANT FLOAT)

INSERT INTO T_HUIT_HUI VALUES (1, 3, 123.45)
INSERT INTO T_HUIT_HUI VALUES (5, 5, 125.43)
INSERT INTO T_HUIT_HUI VALUES (18, 12, 526.28)
INSERT INTO T_HUIT_HUI VALUES (21, 1, 125.85)
INSERT INTO T_HUIT_HUI VALUES (43, 6, 985.42)
INSERT INTO T_HUIT_HUI VALUES (11, 4, 963.25)
INSERT INTO T_HUIT_HUI VALUES (22, 3, 987.45)
INSERT INTO T_HUIT_HUI VALUES (18, 5, 331.84)
INSERT INTO T_HUIT_HUI VALUES (21, 4, 175.85)
INSERT INTO T_HUIT_HUI VALUES (43, 8, 225.48)
INSERT INTO T_HUIT_HUI VALUES (11, 5, 993.25)
INSERT INTO T_HUIT_HUI VALUES (22, 1, 667.49)
INSERT INTO T_HUIT_HUI VALUES (67, 2, 331.84)
INSERT INTO T_HUIT_HUI VALUES (18, 1, 711.89)

-- premère solutation : non relationnelle :

SELECT TOP 8 *
FROM T_HUIT_HUI
ORDER BY HUI_MONTANT DESC

PRS_ID MOI_ID HUI_MONTANT
----------- ----------- --------------------
11 5 993.25
22 3 987.45000000000005
43 6 985.41999999999996
11 4 963.25
18 1 711.88999999999999
22 1 667.49000000000001
18 12 526.27999999999997
67 2 331.83999999999997

--en effet elle s'arrête ici alors qu'il y a un ex aequo !
-->
18 5 331.83999999999997
43 8 225.47999999999999
21 4 175.84999999999999
21 1 125.84999999999999
5 5 125.43000000000001
1 3 123.45

--seconde solution, relationnelle, mais qui intègre (ou non) les ex aequo :

SELECT T1.PRS_ID, T1.MOI_ID, COUNT(T2.PRS_ID)
FROM T_HUIT_HUI T1
LEFT OUTER JOIN T_HUIT_HUI T2
ON T1.HUI_MONTANT < T2.HUI_MONTANT
GROUP BY T1.PRS_ID, T1.MOI_ID
HAVING COUNT(T2.PRS_ID) < 8
ORDER BY 3

PRS_ID MOI_ID
----------- ----------- -----------
11 5 0
22 3 1
43 6 2
11 4 3
18 1 4
22 1 5
18 12 6
18 5 7
67 2 7

A +

--
Frédéric BROUARD - MVP SQL Server, expert SGBDR & SQL
Le site SQL et les SGBDR http://sqlpro.developpez.com
Audit, conseil, formation, modélisation, optimisation
=> http://www.datasapiens.com


"Amirallia" a écrit :

Bonjour,

J'aimerais extraire d'une table, comportant un mois, un idPersone et un
montant, soit les champs idMois, idPerson, Mnt, les 8 plus grands
montants de chaque personne parmi les 12 mois.

Donc ma requete devrait avoir au max 8 lignes par personne.

Une idée ?

J'ai essayé la requete relationelle, mais j'obtiens que les 5
meilleures montants alors que j'ai 12 montant pour une personne(idPerso
= 20420). Voici ma table :

MOI_ID;PRS_ID;MNT
1;20420;6
2;20420;2
3;20420;3
4;20420;1
5;20420;6
6;20420;6
7;20420;1
8;20420;6
9;20420;1
10;20420;9
6;18374;3
6;19846;1
6;20243;5
6;20250;3
6;20259;3
6;20372;5
9;18371;5
9;18380;3
9;19829;5
9;20423;1
9;20424;3
9;20427;5
9;20428;3
9;20786;3
9;22135;6

Pourquoi le ORDER BY 3 ?

>> SELECT TOP 8 WITH TIES *
FROM T_HUIT_HUI
ORDER BY HUI_MONTANT DESC

Que signifie le WITH TIES ?

Lorsque la requête comporte un ORDER BY, il permet d'inclure les dernières
valeurs de colonnes de l'ORDER BY identiques. Dans le cas d'exemple, malgré
le TOP 8, la requête retourne 9 valeurs, parce qu'il y a une valeur de
HUI_MONTANT identique à la huitième ligne retournée.

--
Rudi Bruchez
Consultant indépendant
modélisation, administration, optimisation,
Solutions MS SQL Server et informatique libre.
MCDBA, SCJP2
http://www.babaluga.com/