La fonction SQL ORACLE MAX retourne la valeur la plus grande d’une colonne d’une table ou d’une sélection

SELECT MAX(SAL) FROM EMP ;

MAX(SAL)
----------------------
5000

Il faut savoir que les colonnes nulles sont ignorées dans cette fonction. De plus si jamais nous exécutons cette fonction sur une colonne dont toutes ses valeurs sont nulles alors le résultat est nul aussi

SELECT MAX(COMM) FROM EMP WHERE COMM IS NULL ;

MAX(COMM)
----------------------

MIN et MAX ne servent pas qu’aux nombres

C’est le point le plus souvent ignore : contrairement a SUM et AVG, MAX fonctionne aussi sur les dates et sur le texte. Le critere de comparaison change selon le type.

SELECT MAX(SAL)      AS salaire,
       MAX(HIREDATE) AS embauche,
       MAX(ENAME)    AS nom
  FROM EMP;

SALAIRE  EMBAUCHE   NOM
-------  ---------- ------
5000     12/01/1983 WARD

Attention sur le texte : l’ordre depend du jeu de caracteres et des parametres NLS de la session. Les majuscules et les accents ne se classent pas toujours comme on l’attend. Pour un tri previsible, forcez-le : MAX(NLSSORT(ENAME, 'NLS_SORT=FRENCH')).

Le piege des valeurs NULL

C’est l’erreur la plus courante sur les fonctions d’agregation Oracle : elles ignorent purement et simplement les lignes NULL. Elles ne les comptent pas comme des zeros. La difference se voit immediatement sur la table EMP, ou seuls 4 employes sur 14 ont une commission renseignee.

SELECT COUNT(*)        AS lignes,
       COUNT(COMM)     AS comm_non_nulles,
       SUM(COMM)       AS somme,
       AVG(COMM)       AS moyenne_oracle,
       SUM(COMM)/COUNT(*) AS moyenne_sur_14
  FROM EMP;

LIGNES COMM_NON_NULLES SOMME MOYENNE_ORACLE MOYENNE_SUR_14
------ --------------- ----- -------------- --------------
    14               4  2200            550     157,142857

Oracle divise par 4, pas par 14. Si vous vouliez traiter les commissions absentes comme des zeros, il faut le dire explicitement avec NVL :

SELECT AVG(NVL(COMM, 0)) FROM EMP;   -- 157,142857

Les deux resultats sont justes : ils repondent a deux questions differentes. « Quelle est la commission moyenne de ceux qui en touchent une ? » contre « Quelle est la commission moyenne de l’entreprise ? ». Choisissez en connaissance de cause.

MAX par groupe

SELECT DEPTNO,
       MAX(SAL) AS maxi_service,
       COUNT(*) AS effectif
  FROM EMP
 GROUP BY DEPTNO
 ORDER BY DEPTNO;

DEPTNO MAXI_SERVICE EFFECTIF
------ ------------ --------
    10         5000        3
    20         3000        5
    30         2850        6

Le piege classique : MAX(SAL) ne donne pas QUI

Voici l’erreur que tout le monde commet une fois :

-- FAUX : ORA-00937 fonction de groupe non specifique
SELECT ENAME, MAX(SAL) FROM EMP;

Oracle refuse car ENAME a 14 valeurs alors que MAX(SAL) n’en a qu’une. Trois solutions correctes, par ordre de qualite :

-- 1. La plus lisible : FETCH FIRST (Oracle 12c+)
SELECT ENAME, SAL FROM EMP
 ORDER BY SAL DESC FETCH FIRST 1 ROW ONLY;

-- 2. La plus performante : KEEP DENSE_RANK, un seul balayage
SELECT MAX(ENAME) KEEP (DENSE_RANK FIRST ORDER BY SAL DESC) AS qui,
       MAX(SAL) AS combien
  FROM EMP;

-- 3. La classique : sous-requete
SELECT ENAME, SAL FROM EMP
 WHERE SAL = (SELECT MAX(SAL) FROM EMP);

La solution 3 retourne tous les ex æquo, la solution 1 une seule ligne arbitraire. La 2 est la plus efficace sur grosse table : elle evite le second passage sur les donnees.

MIN et MAX ensemble : l’amplitude

SELECT DEPTNO,
       MIN(SAL) AS mini,
       MAX(SAL) AS maxi,
       MAX(SAL) - MIN(SAL) AS amplitude
  FROM EMP
 GROUP BY DEPTNO
 ORDER BY amplitude DESC;

DEPTNO MINI MAXI AMPLITUDE
------ ---- ---- ---------
    10 1300 5000      3700
    20  800 3000      2200
    30  950 2850      1900

Erreurs frequentes

Sur le même thème

• Fonction SQL ORACLE – RTRIM() et LTRIM()
• Fonction SQL ORACLE – SUBSTR()
• Fonction SQL ORACLE – INSTR()
• Fonction SQL ORACLE – EXTRACT()
• Fonction SQL ORACLE – NVL()
• Fonction SQL ORACLE – NVL2()

Veuillez laisser ce champ vide

Un nouveau tutoriel SQL par semaine

Nous ne spammons pas ! Consultez notre politique de confidentialité pour plus d’informations.

Vérifiez votre boite de réception ou votre répertoire d’indésirables pour confirmer votre abonnement.