How to select only the last entry in my table?

Question

I have the tables Matieres and Resultats

 ___ Matieres ___            ___ Resultats ___
    id (int auto increment)      ID (int auto_increment)
    Nom (varchar)            TIME_OF_INSERTION (datetime current_timestamp)
    Categorie (varchar)          ID_USER (int)
    Active (boolean)             ID_MATIERE (int)
                                 RESULTAT (int)

And i have this request:

SELECT * FROM Matieres LEFT JOIN Resultats ON Matieres.id = Resultats.ID_MATIERE AND Resultats.ID_USER = :userid WHERE Active = TRUE AND ID_Formation = :formation)

The problem is that it returns me every resultats the user inserted in the table Resultats, for each Matieres. When i just want the last one inserted for each Matieres.Nom ... :/

I tried with GROUP BY TIME_OF_INSERTION but it won't work :(

Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — astentx, Nov 06 '20 at 12:58

score 1 · Accepted Answer · answered Nov 06 '20 at 12:48

1

You can use row_number():

SELECT *
FROM Matieres m LEFT JOIN
     (SELECT r.*,
             ROW_NUMBER() OVER (PARTITION BY id_Matiere, id_user ORDER BY time_of_insertions DESC) as seqnum
      FROM Resultats r
      WHERE r.ID_USER = :userid 
     ) r
     ON m.id = r.ID_MATIERE AND
        seqnum = 1
WHERE Active = TRUE AND ID_Formation = :formation;

answered Nov 06 '20 at 12:48

Gordon Linoff

1,242,037
58
646
786

Your SQL request works perfectly ! Problem solved ! 1000 times thx to you ! :) – The-Evil-Fox Nov 06 '20 at 13:10

score 0 · Answer 2 · answered Nov 06 '20 at 12:51

0

try adding index 1 at the end of the query for the last and only 1 record

answered Nov 06 '20 at 12:51

Saad Javaid Alvi

1
2

How to select only the last entry in my table?

2 Answers2