Group by avec la fonction Max()

Bonjour à tous,

Je voudrais récupérer une (et une seule) ligne dans ma table en filtrant sur le champs date le plus récent.

Structure de ma table, qu'on va appeler TABLE_TEST

Code:

---

1 2 3 4 5 6 7

|DATE |VALEUR1 |VALEUR2 | |200501 | TOTO | TRUC | |200605 | TOTO | TRUC | |200702 | TOTO | MACHIN | |200602 | TITI | MACHIN | |200701 | TATA | CHOSE |

---

et je lance cette requête :

Code:

---

1 2 3 4

select MAX(to_number(A.DATE)), A.VALEUR1, A.VALEUR2 from TABLE_TEST A group by A.DATE, A.VALEUR1, A.VALEUR2

---

mais ça me renvoie ça :

Code:

---

1 2 3 4 5 6

|DATE |VALEUR1 |VALEUR2 | |200605 | TOTO | TRUC | |200702 | TOTO | MACHIN | |200602 | TITI | MACHIN | |200701 | TATA | CHOSE |

---

ce qui est normal, je l'admets, puisque je groupe par VALEUR1 et VALEUR2
mais moi ce que je voudrais c'est arriver à ce résultat :

Code:

---

1 2 3

|DATE |VALEUR1 |VALEUR2 | |200702 | TOTO | MACHIN |

---

mais quand j'enlève les deux champs dans le group by, il me fait une erreur...


Merci de votre aide! ^^

NB : l'intitulé du champs date est trompeur, en fait ce sont des varchar2(6)

si ce que tu veux c'est avoir valeur 1 et 2 pour la date max
alors :

Code:

---

1 2 3 4 5

SELECT A.DATE, A.VALEUR1, A.VALEUR2 FROM TABLE_TEST A where a.date=(select MAX(b.DATE) from table_test b)

---

Citation:

---

Envoyé par c.langlet

si ce que tu veux c'est avoir valeur 1 et 2 pour la date max
alors :

Code:

---

1 2 3 4 5

SELECT A.DATE, A.VALEUR1, A.VALEUR2 FROM TABLE_TEST A where a.date=(select MAX(b.DATE) from table_test b)

---

---

pour une question de tuning il faut éviter les requetes imbriquées
la solution passe par un bloc WITH que tu mets avant.

je t'invite à faire une recherche sur le forum pour trouver des posts sur ce sujet

Citation:

---

Envoyé par PpPool

pour une question de tuning il faut éviter les requetes imbriquées
la solution passe par un bloc WITH que tu mets avant.

je t'invite à faire une recherche sur le forum pour trouver des posts sur ce sujet

---

Bonjour,

Ta réponse concernant les blocs WITH m'intéresse. J'ai cherché sur le forum mais je n'ai pas trouvé. Pour moi, la solution est d'utiliser un bloc "not exists" à la place de la requête imbriquée, par contre je n'ai jamais entendu parlé de ces blocs With. Si tu pouvais me guider sur une piste merci !
Avec le not exists :

Code:

---

1 2 3 4 5 6

SELECT A.DATE, A.VALEUR1, A.VALEUR2 FROM TABLE_TEST A WHERE not exists (select 1 from table_test b where b.date > a.date )

---

essayes

Code:

---

1 2 3 4 5 6

SELECT A.DATE, A.VALEUR1, A.VALEUR2 FROM TABLE_TEST A, (select MAX(A.DATE) date FROM TABLE_TEST) T where A.date = T.date

---

Ca devrait etre aussi efficace que with, comme on n'utilise qu'une fois la valeur retournée

Le WITH permet d'aliaser une sous-requête avant la requête proprement dite.
Par contre je n'ai pas trouvé plus d'éclaircissement quant à l'optimisation apporté par cette syntaxe (exécution unique ?).

En tout cas voici pour la syntaxe :

Code:

---

1 2 3

WITH SR AS (SELECT MAX(DATE) date_max FROM TABLE_TEST) SELECT * FROM TABLE_TEST WHERE date = SR.date_max

---

Citation:

---

Envoyé par Waldar

Le WITH permet d'aliaser une sous-requête avant la requête proprement dite.
Par contre je n'ai pas trouvé plus d'éclaircissement quant à l'optimisation apporté par cette syntaxe (exécution unique ?).

En tout cas voici pour la syntaxe :

Code:

---

1 2 3

WITH SR AS (SELECT MAX(DATE) date_max FROM TABLE_TEST) SELECT * FROM TABLE_TEST WHERE date = SR.date_max

---

---

Attention, il faut ajouter ta table SR, ainsi :

Code:

---

1 2 3 4 5 6 7 8

WITH SR AS ( SELECT MAX(DATE) AS date_max FROM TABLE_TEST) SELECT * FROM TABLE_TEST, SR WHERE date = SR.date_max

---

voilà qui est mieux
le bloc WITH est une parade très performante pour éviter les requetes imbriquées, il n'est exécuté qu'une seule fois
après c'est comme tout, il faut comparer les explain plan
je ne me suis pas plus attardé que çà sur ta requete, mais il me semble aussi que tu devrais regarder les fonctions analytiques, tu as un tuto excellent sur DVP

Merci pour la correction (nécessaire) et les précisions.

Salut !

Je suis pas sûr de ça PpPool...
A priori, il se peut que la requête de Waldar fasse la même chose.
Ce qu'il peut se passer, c'est que le CBO choissise de merger la vue (c'est à dire la sous requête d'agrégat) dans la requête principale, ce qui pourrirait les perfs.

Mais si tu ajoutes le hint : /*+ no_merge(TonAlias) */, tu obtiens le même résultat, soit une VIEW sur le SORT GROUP BY. (la sous-requête n'est pas mergée dans la requête principale)

Quand tu utilises le WITH, tu peux utiliser le hint /*+materialize */ pour être sûr que la vue (définie dans le with) soit matérialisée par Oracle.
(faites le test, et regardez l'explain plan, c'est rigolo).

Voilà voilà...

Hello,

L'utilisation d'une fonction analytique permet d'obtenir le résultat que tu veux de façon optimisée.

Code:

---

1 2 3 4

SELECT MAX(TO_NUMBER(a.date)), MIN (a.valeur1) KEEP (DENSE_RANK FIRST ORDER BY TO_NUMBER(a.date) DESC), MIN (a.valeur2) KEEP (DENSE_RANK FIRST ORDER BY TO_NUMBER(a.date) DESC) FROM table_test a;

---

Si tu regardes l'explain plan, la table n'est accédée qu'une seule fois.

Citation:

---

Envoyé par xdescamp

Hello,

L'utilisation d'une fonction analytique permet d'obtenir le résultat que tu veux de façon optimisée.

Code:

---

1 2 3 4

SELECT MAX(TO_NUMBER(a.date)), MIN (a.valeur1) KEEP (DENSE_RANK FIRST ORDER BY TO_NUMBER(a.date) DESC), MIN (a.valeur2) KEEP (DENSE_RANK FIRST ORDER BY TO_NUMBER(a.date) DESC) FROM table_test a;

---

Si tu regardes l'explain plan, la table n'est accédée qu'une seule fois.

---

Ce n'est pas une fonction analytique dans ton exemple.
Voilà la diagramme de syntaxe pour la version analytique

Citation:

---

DENSE_RANK( ) OVER([query_partition_clause] order_by_clause)

---

OK, au temps pour moi.