récente - sql select enregistrement le plus recent




Meilleure façon de sélectionner la ligne avec l'horodatage le plus récent correspondant à un critère (7)

C'est quelque chose qui arrive si souvent que j'ai presque cessé d'y penser mais je suis presque certain que je ne le fais pas de la meilleure façon.

La question: Supposons que vous avez le tableau suivant

CREATE TABLE TEST_TABLE
(
  ID          INTEGER,
  TEST_VALUE  NUMBER,
  UPDATED     DATE,
  FOREIGN_KEY INTEGER
);

Quelle est la meilleure façon de sélectionner le TEST_VALUE associé à la dernière ligne mise à jour où FOREIGN_KEY = 10?

EDIT: Rendons ceci plus intéressant car les réponses ci-dessous vont simplement avec ma méthode de tri puis en sélectionnant la ligne du haut. Pas mal mais pour les gros retours, l'ordre tuerait la performance. Donc, les points bonus: comment le faire de manière évolutive (c'est-à-dire sans l'ordre inutile par).


SELECT TEST_VALUE
  FROM TEST_TABLE
 WHERE UPDATED      = ( SELECT MAX(UPDATED)
                          FROM TEST_TABLE
                         WHERE FOREIGN_KEY = 10 )
   AND FOREIGN-KEY  = 10
   AND ROWNUM       = 1  -- Just in case records have the same UPDATED date

Plutôt que de prendre le premier enregistrement, vous pouvez rompre une cravate avec le plus gros ID ou peut-être le moins / le plus grand TEST_VALUE.

Un index de FOREIGN_KEY, UPDATED aiderait la requête performace.



La performance dépendra de ce qui est indexé. Voici une méthode.

WITH 
ten AS
(
    SELECT *
    FROM TEST_TABLE
    WHERE FOREIGH_KEY = 10
)
SELECT TEST_VALUE 
FROM ten
WHERE UPDATED = 
(
    SELECT MAX(DATE)
    FROM ten
)

Les fonctions analytiques sont vos amis

SQL> select * from test_table;

        ID TEST_VALUE UPDATED   FOREIGN_KEY
---------- ---------- --------- -----------
         1         10 12-NOV-08          10
         2         20 11-NOV-08          10

SQL> ed
Wrote file afiedt.buf

  1* select * from test_table
SQL> ed
Wrote file afiedt.buf

  1  select max( test_value ) keep (dense_rank last order by updated)
  2  from test_table
  3* where foreign_key = 10
SQL> /

MAX(TEST_VALUE)KEEP(DENSE_RANKLASTORDERBYUPDATED)
-------------------------------------------------
                                               10

Vous pouvez également étendre cela pour obtenir les informations pour toute la ligne

SQL> ed
Wrote file afiedt.buf

  1  select max( id ) keep (dense_rank last order by updated) id,
  2         max( test_value ) keep (dense_rank last order by updated) test_value
,
  3         max( updated) keep (dense_rank last order by updated) updated
  4  from test_table
  5* where foreign_key = 10
SQL> /

        ID TEST_VALUE UPDATED
---------- ---------- ---------
         1         10 12-NOV-08

Et les approches analytiques sont généralement assez efficaces.

Je devrais également souligner que les fonctions analytiques sont relativement nouvelles, donc si vous êtes sur quelque chose avant 9.0.1, cela peut ne pas fonctionner. Ce n'est plus une population énorme, mais il y a toujours quelques personnes coincées sur les anciennes versions.


Utilisez une sous-requête

WHERE updated = (SELECT MAX(updated) ...)

ou sélectionnez le TOP 1 avec

ORDER BY updated DESC

Dans la syntaxe Oracle, ce serait:

SELECT 
  * 
FROM 
(
  SELECT * FROM test_table
  ORDER BY updated DESC
)
WHERE 
  ROWNUM = 1

cela ne fonctionnerait-il pas?

SELECT TOP 1 ID
FROM test_table
WHERE FOREIGN_KEY = 10
ORDER BY UPDATED DESC

pas besoin de sous-requête ...


Jusqu'à ce que je lise la réponse de Justin Cave, j'ai utilisé le modèle suivant pour récupérer les disques les plus récents en masse.

WITH test_table_ranked AS (
    SELECT
        test_table.*,
        ROW_NUMBER() OVER (
            PARTITION BY foreign_key ORDER BY updated DESC
        ) AS most_recent
    FROM
        test_table
)
SELECT *
FROM test_table_ranked
WHERE most_recent = 1
-- AND foreign_key = 10

Cette requête trouve les mises à jour les plus récentes pour chaque clé étrangère dans la table. Bien que la réponse de Justin soit plus rapide lorsque la clé est connue, cette requête fonctionne également dans SQL Server.





oracle