Date max ou null

Version imprimable

17/12/2018, 11h50
orneen

Date max ou null
Bonjour,

j'ai une table qui ressemble à :

identifiant;datdeb;datfin
1;01/01/2015;12/02/2016
1;13/02/2016;18/11/2017
1;19/11/2017;<null>
2;01/02/2012;18/11/2015
2;19/11/2015;21/12/2016
2;22/12/2016;31/12/2017

je cherche à récupérer dans une requête pour chaque identifiant la dernière date de fin connue. Dans l'exemple ci-dessus je voudrais obtenir :

1;<null>
2;31/12/2017

La requête que j'utilise est :
Code:

1 2 3 SELECT a.identifiant, a.datfin FROM matable a WHERE a.datfin is null or datfin = (select max(datfin) from matable b where b.identifiant = a.identifiant);
Or j'obtiens

1;18/11/2017
1;<null>
2;31/12/2017

Où est mon erreur ?

Merci de votre aide
17/12/2018, 12h14
Sunchaser
Bonjour,

A ce que je comprends des données en exemple, je me baserais plutôt sur datdeb, et donc je partirais sur un truc comme:
Code:

1 2 3 4 SELECT a.identifiant, a.datfin FROM matable a WHERE a.datdeb = (select max(datdeb) from matable b where b.identifiant = a.identifiant);
@+
;)

Bonjour,

Tu fais un OR, donc

Code:

1
2
3
SELECT a.identifiant, a.datfin
FROM matable a
WHERE a.datfin is null

va renvoyer la ligne
1;19/11/2017;<null>

Code:

1
2
3
SELECT a.identifiant, a.datfin
FROM matable a
WHERE datfin = (select max(datfin) from matable b where b.identifiant = a.identifiant);

Va renvoyer les 2 lignes
1;18/11/2017
2;31/12/2017

Ca dépend aussi ce que tu entends par "dernière date de fin connue", ainsi que les données qui sont dans la table

Tu pourrais faire un group by et récupérer le max de datfin, mais vu que le MAX ignore les valeurs NULL, il faut gérer le null en y affectant une date supérieure à tout ce qui peut exister en base.
Exemple

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH T AS (SELECT 1 identifiant, TO_DATE('01/01/2015','DD/MM/RRRR') datdeb, TO_DATE('12/02/2016', 'DD/MM/RRRR') datfin FROM dual
UNION ALL SELECT 1, TO_DATE('13/02/2016','DD/MM/RRRR') datdeb, TO_DATE('18/11/2017', 'DD/MM/RRRR') datfin FROM dual
UNION ALL SELECT 1, TO_DATE('19/11/2017','DD/MM/RRRR') datdeb, TO_DATE('', 'DD/MM/RRRR') datfin FROM dual
UNION ALL SELECT 2, TO_DATE('01/02/2012','DD/MM/RRRR') datdeb, TO_DATE('18/11/2015', 'DD/MM/RRRR') datfin FROM dual
UNION ALL SELECT 2, TO_DATE('19/11/2015','DD/MM/RRRR') datdeb, TO_DATE('21/12/2016', 'DD/MM/RRRR') datfin FROM dual
UNION ALL SELECT 2, TO_DATE('22/12/2016','DD/MM/RRRR') datdeb, TO_DATE('31/12/2017', 'DD/MM/RRRR') datfin FROM dual)
SELECT identifiant, CASE WHEN MAX(NVL(datfin, TO_DATE('31.12.9999', 'DD.MM.RRRR'))) = TO_DATE('31.12.9999', 'DD.MM.RRRR') THEN NULL ELSE MAX(datfin) END datefin
FROM t
GROUP BY identifiant
 
IDENTIFIANT	DATEFIN
1	
2	31/12/2017

17/12/2018, 12h28
orneen

Déjà merci de vos réponses :

Pour répondre à Sunchaser, j'avais déjà penser à cette idée mais il arrive que j'ai des cas avec plusieurs fois la même date de début dont la seule que je veux récupérer est celle qui a une valeur null en date de fin. Du coup ça ne marche pas.

Pour répondre à Mcm, le problème de ta solution c'est qu'elle m'oblige à faire autant de ligne que d'identifiant or j'ai une table avec plusieurs milliers de ligne. L'exemple que j'ai fourni est une version très simplifié de la table.

Je suis entrain de regarder pour faire la chose en PL/SQL mais ça m'embête de sortir l'artillerie lourde pour un truc qui me paraît assez simple (bien que je m'en sorte pas :))

Pour compléter mon propos ce que j'entends par dernier date de fin connue c'est null si la ligne existe ou la date max si pas de null.

Si j'ai bien compris, tu veux faire ressortir les dates de fin à NULL et, s'il n'y en a pas, la date maximale.
Mise en oeuvre de la règle telle qu'énoncée :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT  *
FROM    matable
WHERE   datfin  IS NULL   -- date finale est NULL
UNION ALL
SELECT  *
FROM    matable tb1
WHERE   NOT EXISTS   -- pas de date finale à NULL
        (   SELECT  NULL
            FROM    matable tb2
            WHERE   tb2.datfin  IS NULL
                AND tb2.identifiant = tb1.identifiant
        )
    AND tb1.datfin = (  SELECT  MAX(tb3.datfin) -- La dernière date
                        FROM    matable tb3
                        WHERE   tb3.identifiant = tb1.identifiant
                    )
;

17/12/2018, 13h46
orneen

Merci beaucoup al1_24 je savais bien qu'il y avait une solution à mon problème sans que cela devienne une usine à gaz.

FIRST et LAST dans leurs versions aggrégées :

Code:

1
2
3
4
SELECT a.identifiant
     , max(a.datfin) keep (dense_rank first order by a.datfin desc nulls first)
  FROM matable a
 group by a.identifiant

17/12/2018, 14h39
McM
Citation:

Envoyé par orneen

Pour répondre à Mcm, le problème de ta solution c'est qu'elle m'oblige à faire autant de ligne que d'identifiant or j'ai une table avec plusieurs milliers de ligne. L'exemple que j'ai fourni est une version très simplifié de la table.

Comment ça autant de ligne ?
Le WITH c'était pour avoir un jeu de données, le SELECT se résume à
Code:

1 2 3 SELECT identifiant, CASE WHEN MAX(NVL(datfin, TO_DATE('31.12.9999', 'DD.MM.RRRR'))) = TO_DATE('31.12.9999', 'DD.MM.RRRR') THEN NULL ELSE MAX(datfin) END datefin FROM t GROUP BY identifiant
17/12/2018, 16h40
orneen

Merci skuatamad et Mcm. Vos solutions fonctionne mais si j'ajoute la moindre colonne supplémentaire alors là ça ne marche plus. Or l'exemple que j'ai donné n'est qu'un extrait du résultat attendu et donc il ne fonctionne pas comme je le souhaiterai.

C'est possible de rajouter des colonnes :

Code:

1
2
3
4
5
SELECT a.identifiant
     , max(a.datfin) keep (dense_rank first order by a.datfin desc nulls first)
	 , max(a.une_autre colonne) keep (dense_rank first order by a.datfin desc nulls first)
  FROM matable a
 group by a.identifiant

Mais s'il y a beaucoup de colonnes le plus simple et lisible est probablement avec row_number :

Code:

1
2
3
4
5
6
7
8
  with t as (
SELECT a.*
     , row_number() over (partition a.identifiant order by a.datfin desc NULLS first) as rn
FROM matable a
)
select *
  from t
 where rn = 1

18/12/2018, 15h03
SQLpro

et pourquoi pas un COALESCE(datfin, '9999-12-31') ?

A +