Oracle SQL GROUP BY “et non à un GROUPE PAR l'expression” aide

J'ai une table some_table comme

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   |
| 2      | 2        | 2011-04-20 21:03:04 | 229   |
| 3      | 3        | 2011-04-20 21:03:03 | 130   |
| 4      | 1        | 2011-04-20 21:02:09 | 97    |
| 5      | 2        | 2011-04-20 21:02:08 | 65    |
| 6      | 3        | 2011-04-20 21:02:07 | 101   |
| ...    | ...      | ...                 | ...   |
+--------+----------+---------------------+-------+

Et je veux les derniers dossiers pour la other_id 1, 2, et 3. L'évidence de la requête, je suis venu avec est

SELECT id, other_id, MAX(date_value), value
  FROM some_table 
 WHERE other_id IN (1, 2, 3) 
 GROUP BY other_id

Cependant, il crache un "not a GROUP BY expression" l'exception". J'ai essayé d'ajouter tous les autres champs (c'est à dire id, value) dans le GROUP BY clause, mais qui ne renvoie que tout, exactement comme s'il n'y avait pas de GROUP BY clause. (Eh bien, c'est logique aussi.)

Alors... je suis en train de lire l'Oracle SQL manuel, et tout ce que je peux trouver sont quelques exemples faisant intervenir uniquement des requêtes avec deux ou trois colonnes et quelques je-avez-jamais-vu-avant de fonctions de regroupement. Comment puis-je aller et retour

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   |
| 2      | 2        | 2011-04-20 21:03:04 | 229   |
| 3      | 3        | 2011-04-20 21:03:03 | 130   |
+--------+----------+---------------------+-------+

(les dernières entrées pour chaque other_id) ? Merci.

OriginalL'auteur Yanick Rochon | 2011-04-28

  1. 15
     select id, other_id, date_value, value from
 (
   SELECT id, other_id, date_value, value, 
   ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
   FROM some_table 
   WHERE other_id IN (1, 2, 3) 
 )
 where r = 1
    excellent! cela fonctionne comme un charme!
    Pourquoi ne pas utiliser l'agrégat MAX?
    le order by date_value desc fait la même chose
    Rochon: Oui, mais le tri a toujours un coût. Probablement à l'aide de MAX est plus efficace.

    OriginalL'auteur

  2. 8

    Vous ne pouvez pas SÉLECTIONNER une colonne qui n'est pas un agrégat ou calculée uniquement à partir des colonnes utilisées dans la clause GROUP BY.

    Cependant, il y a trois façons de le faire:

    • Vous pouvez utiliser des fonctions analytiques

      SELECT id, other_id, date_value, value
  FROM ( SELECT id, other_id, date_value, MAX(date_value) OVER (partition by other_id) max_date, value
           FROM some_table )
 WHERE max_date = date_value;

    • Vous pouvez utiliser une auto-jointure avec un “plus grand que ” la clause et de détecter votre max de cette façon

      SELECT t1.id, t1.other_id, t1.date_value, t1.value
  FROM some_table t1
  LEFT OUTER JOIN some_table t2
               ON ( t1.other_id = t2.other_id AND t2.date_value > t1.date_value )
 WHERE t2.other_id IS NULL

    • Vous pouvez utiliser une sous-requête

        WITH max AS ( SELECT other_id, MAX(date_value) FROM some_table GROUP BY other_id )
SELECT id, other_id, date_value, value
  FROM some_table
 WHERE ( other_id, date_value ) IN ( SELECT * FROM max )
    la première requête se termine par un max_date not a valid identifier
    Rochon: oui, en fait, cette requête doit être imbriquées aussi. Je vais le réécrire un peu. Les autres requêtes?
    oui, la seconde fonctionne, mais plus lent que Michael (0.030 ms par rapport à 0,010 ms en moyenne)
    Rochon: Pas étonnant, les fonctions analytiques sont conçus à cette fin. L'auto-jointure est donc plus cher (mais il est toujours disponible si vous passez à un autre SGBDR).
    merci pour votre aide

    OriginalL'auteur

  3. 0

    C'est probablement la façon la plus simple

    SELECT id, other_id, date_value, value
FROM some_table
WHERE date_value in (SELECT MAX(date_value)
                     from some_table
                     GROUP BY other_id
                     HAVING other_id in (1,2,3));

    Test de la requête ci-dessus ici

    OriginalL'auteur