Une requête SQL retournant un élément bien précis

la dernière occurence entrée

Sujet résolu

17 novembre 2010 à 23:23:47

Bonjour,

je souhaiterai pouvoir récupérer en SQL la dernière occurence entrée dans une table (c'est à dire celle avec le plus grand timestamp ou le plus grand id). Bien sur je pourrai récupérer toute la table puis chercher le maximum mais bon c'est pas top...
Est ce qu'il existe une instruction me permettant directement de récupérer le dernier élément ou le maximum ?

Merci d'avance pour vos réponses.
Bonne soirée.

PS : si j'ai pas été clair, ne pas hésiter à me demander de préciser.

Note : j'ai par ailleurs un gros problème avec un .htaccess : http://www.siteduzero.com/forum-83-578811-p1-htaccess-et-redirection.html#r5612712 :euh:

Merci.

Fayden

17 novembre 2010 à 23:27:15

Avec ORDER BY + LIMIT :

SELECT colonne1, colonne2, colonne3
FROM ta_table
ORDER BY id DESC
LIMIT 1;

P.S. En parlant de timestamp, je suppose que tu veux dire le timestamp unix (ce que renvoie la fonction time()). Note que c'est une très mauvaise pratique que de stocker le timestamp unix dans ta base de données, mieux vaut utiliser le type DATETIME qui sert réellement à stocker des dates. Le tutoriel de M@teo21 en parle.

Hawks

18 novembre 2010 à 0:53:59

Si c'est suite à une insertion, utilise plutôt LAST_INSERT_ID

Ilae

18 novembre 2010 à 0:57:53

Ok merci beaucoup Fayden pour ta réponse. Ca marche très bien. (j'aurai du y penser en plus >< )
Et merci à toi aussi Hawks, je testerai ça demain pour voir comment ça marche.

Bonne nuit.

Hawks

18 novembre 2010 à 1:08:44

Pour info, le gros intérêt de ma méthode, c'est que si deux utilisateurs font une insertion en même temps, en récupérant le dernier ID, ils auront tous les deux le même, en récupérant le dernier ID qu'ils ont insérés (respectivement), ils auront chacun le leur

devil may cry

18 novembre 2010 à 1:26:58

Citation : Fayden

Avec ORDER BY + LIMIT :
SELECT colonne1, colonne2, colonne3
FROM ta_table
ORDER BY id DESC
LIMIT 1;
P.S. En parlant de timestamp, je suppose que tu veux dire le timestamp unix (ce que renvoie la fonction time()). Note que c'est une très mauvaise pratique que de stocker le timestamp unix dans ta base de données, mieux vaut utiliser le type DATETIME qui sert réellement à stocker des dates. Le tutoriel de M@teo21 en parle.

Je ne suis pas certain que ce soit l'idéal cette requête. Un tri (donc coût non négligeable) avec une limitation à une ligne,c'est un peu du gaspillage.

Je préconiserai plutôt :

SELECT colonne1, colonne2, colonne3
FROM ta_table
WHERE id=MAX(id);

-- si la requête précédente ne marche pas
SELECT colonne1, colonne2, colonne3
FROM ta_table
WHERE id=(SELECT MAX(id) FROM ta_table);

Fayden

18 novembre 2010 à 1:43:29

Je me doutais que quelqu'un allait proposer ça, j'aurais dû anticiper et faire une demo vite faite. La requête avec la sous-requête est plus lente qu'un ORDER BY + LIMIT (d'ailleurs, la première ne fonctionnerait même pas). Voyons voir avec PostgreSQL et une table d'un million de lignes :

vsavard_db=> CREATE TABLE test AS SELECT n FROM generate_series(1, 1000000) n;
vsavard_db=> SELECT n FROM test WHERE n = (SELECT MAX(n) FROM test);
    n    
---------
 1000000
(1 ligne)

Temps : 522,417 ms
vsavard_db=> SELECT n FROM test ORDER BY n DESC LIMIT 1;
    n    
---------
 1000000
(1 ligne)

Temps : 294,579 ms
vsavard_db=> EXPLAIN ANALYZE SELECT n FROM test WHERE n = (SELECT MAX(n) FROM test);
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=16422.01..32844.01 rows=1 width=4) (actual time=3732.659..3732.661 rows=1 loops=1)
   Filter: (n = $0)
   InitPlan 1 (returns $0)
     ->  Aggregate  (cost=16422.00..16422.01 rows=1 width=4) (actual time=3496.780..3496.782 rows=1 loops=1)
           ->  Seq Scan on test  (cost=0.00..13922.00 rows=1000000 width=4) (actual time=0.016..1747.110 rows=1000000 loops=1)
 Total runtime: 3732.738 ms
(6 lignes)

Temps : 3733,428 ms
vsavard_db=> EXPLAIN ANALYZE SELECT n FROM test ORDER BY n DESC LIMIT 1;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=18922.00..18922.00 rows=1 width=4) (actual time=3524.775..3524.777 rows=1 loops=1)
   ->  Sort  (cost=18922.00..21422.00 rows=1000000 width=4) (actual time=3524.768..3524.768 rows=1 loops=1)
         Sort Key: n
         Sort Method:  top-N heapsort  Memory: 17kB
         ->  Seq Scan on test  (cost=0.00..13922.00 rows=1000000 width=4) (actual time=0.070..1752.879 rows=1000000 loops=1)
 Total runtime: 3524.848 ms
(6 lignes)

Temps : 3525,364 ms

Et avec un index :

vsavard_db=> CREATE INDEX idx_test_n ON test (n);
CREATE INDEX
Temps : 3316,419 ms
vsavard_db=> SELECT n FROM test WHERE n = (SELECT MAX(n) FROM test);
    n    
---------
 1000000
(1 ligne)

Temps : 0,709 ms
vsavard_db=> SELECT n FROM test ORDER BY n DESC LIMIT 1;
    n    
---------
 1000000
(1 ligne)

Temps : 0,532 ms
vsavard_db=> EXPLAIN ANALYZE SELECT n FROM test WHERE n = (SELECT MAX(n) FROM test);
Temps : 0,926 ms
vsavard_db=> EXPLAIN ANALYZE SELECT n FROM test ORDER BY n DESC LIMIT 1;
Temps : 0,623 ms

Le plan d'exécutions des deux dernières requêtes (en ordre d'apparition) :

QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=0.028..0.030 rows=1 loops=1)
   ->  Index Scan Backward using idx_test_n on test  (cost=0.00..27717.34 rows=1000000 width=4) (actual time=0.023..0.023 rows=1 loops=1)
 Total runtime: 0.074 ms
(3 lignes)

 QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_test_n on test  (cost=0.04..8.39 rows=1 width=4) (actual time=0.066..0.070 rows=1 loops=1)
   Index Cond: (n = $1)
   InitPlan 2 (returns $1)
     ->  Result  (cost=0.03..0.04 rows=1 width=0) (actual time=0.042..0.045 rows=1 loops=1)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=0.031..0.033 rows=1 loops=1)
                   ->  Index Scan Backward using idx_test_n on test  (cost=0.00..27717.34 rows=1000000 width=4) (actual time=0.026..0.026 rows=1 loops=1)
                         Filter: (n IS NOT NULL)
 Total runtime: 0.138 ms
(9 lignes)

Un MAX implique, en quelque part, un tri. Pas le choix : il faut bien comparer avec les autres valeurs possibles. Je suis pas très doué pour expliquer les explain analyze, Lord Casque Noir ou Cintre Sournois le font bien mieux que moi, mais je ne crois pas me tromper en affirmant qu'on voit bien que la méthode avec ORDER BY + LIMIT est plus rapide que MAX.

devil may cry

18 novembre 2010 à 2:03:13

Intéressant à savoir. (attention par contre , limit n'existe pas en SQL pur

)
En est il de même pour un timestamp ? (çad que le plus gros timestamp se situe de façon aléatoire (vers le milieu) et pas en dernière position)
Très étonnant, parce que ton tri va devoir utiliser une quantité de mémoire phénoménale alors que max est juste une variable comparée.
Même un quick_sort() est en complexité (n log n) avec que un max (d'instinct) serait en (n).
Je sais que certains tris sont plus efficaces si on connait le nombre d'éléments mais dans un tableau séquentiel.

Je serais vraiment curieux de connaitre la raison.

Fayden

18 novembre 2010 à 2:14:10

Il n'y a pas d'ordre dans une table. Parler de première ou de dernière position n'a pas réellement de sens. Et pour les index, si j'ai bien compris, les b-tree (ceux par défaut) sont déjà ordonnés (de façon ascendante).

Après, timestamp ou pas (si tu parles des timestamps unix, je répète qu'ils n'ont rien à faire dans une base de données, il y a d'autres types pour ça), je doute que ça fasse une différence. Un entier est un entier, et c'est pas une poignée de kilooctets qui va faire peur à un serveur. Donc je qualifierais pas du tout ça comme une quantité phénoménale de mémoire.

Pour la vraie raison qui ferait que ORDER BY + LIMIT serait plus rapide que MAX, je vais éviter de m'aventurer dans des chemins inconnus et attendre la venue de Lord Casque Noir ou Cintre Sournois qui sont beaucoup plus informés que moi. Ils pourront t'apporter une réponse plus complète (tu peux leur envoyer un message privé, je crois pas que ça les dérangerait).

Et concernant LIMIT, la syntaxe LIMIT X OFFSET Y de PostgreSQL / MySQL n'est pas exactement celle de la norme, mais il existe bien un LIMIT normalisé depuis SQL:2008 (source).