Optimisation de recherche de max

**tchoimars** · 30/11/2009, 11h19

Bonjour,

j'aimerais récupérer dans une table le max d'un montant avec des données associées et le tout groupé par "Nom" et "numéro de contrat".
Actuellement j'ai ça

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT  C1.NOM,
            C1.NUM_CTA,
            C1.MNT_FACTURE,
            C1.PRENOM,
            C1.ADRESSE
FROM    CLIENT C1
WHERE   C1.MNT_FACTURE = (  SELECT MAX(C2.MNT_FACTURE) 
                            FROM    CLIENT C2
                            WHERE   C1.NOM      = C2.NOM
                            AND     C1.NUM_CTA  = C2.NUM_CTA)

Le problème c'est que je me fais 2 lectures full sur ma table CLIENT qui fait presque 1 million de lignes, ce qui prend du temps.

Bref j'aimerais faire un truc optimisé mais je ne trouve aucune bonne idée
du style

Code :

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

1
2
3
4
5
6
7
8
 
SELECT  C1.NOM,
            C1.NUM_CTA,
            MAX(C1.MNT_FACTURE),
            C1.PRENOM
            C1.ADRESSE
FROM    CLIENT C1
GROUP BY C1.NOM,  C1.NUM_CTA

Alors forcément ça ne marche pas le truc au dessus mais c'est comme ça que j'aimerais faire

Si quelqu'un a une bonne réponse à m'apporter je suis preneur
Merci

**pacmann** · 30/11/2009, 11h47

Salut !

Je suppose que tu peux avoir plusieurs lignes correspondant au max ?
Dans ce cas, les fonctions analytiques devraient pouvoir t'aider :
Tu classes tes lignes par mnt_facture décroissant, en donnant le même rang au ex-aequo,puis tu filtres.

Code :

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

1
2
3
4
5
6
 
SELECT * FROM (
SELECT dense_rank() over(order by mnt_facture desc) as rk, c1.*
FROM CLIENT c1
)
WHERE rk = 1

**dariyoosh** · 30/11/2009, 14h52

Envoyé par pacmann

Salut !

Je suppose que tu peux avoir plusieurs lignes correspondant au max ?
Dans ce cas, les fonctions analytiques devraient pouvoir t'aider :
Tu classes tes lignes par mnt_facture décroissant, en donnant le même rang au ex-aequo,puis tu filtres.

Code :

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

1
2
3
4
5
6
 
SELECT  FROM (
SELECT dense_rank() over(order by mnt_facture desc) as rk, c1.*
FROM CLIENT c1
)
WHERE rk = 1

Bonjour,

C'est une belle solution!

Juste une petite ramarque, sachant qu'il faut grouper selon "nom" et "NUM_CTA", il manque une clause PARTITION BY

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT *
FROM (SELECT nom,
                    prenom,
                    adresse,
                    mnt_facture,
                    num_cta,
                    dense_rank() over(PARTITION BY nom, num_cta ORDER BY mnt_facture DESC) AS rk) sbq1
WHERE (sbq1.rk = 1)