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.
Il y a une FAQ SQL Oracle qui peut vous aider:
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)
-------------------------------------------------
10Vous 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-08Et 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 DESCDans la syntaxe Oracle, ce serait:
SELECT
*
FROM
(
SELECT * FROM test_table
ORDER BY updated DESC
)
WHERE
ROWNUM = 1cela ne fonctionnerait-il pas?
SELECT TOP 1 ID
FROM test_table
WHERE FOREIGN_KEY = 10
ORDER BY UPDATED DESCpas 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 = 10Cette 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.
