NidalM NidalM - 4 months ago 8
SQL Question

Get only one entry for columns that have the same id

I have two tables: the first one is
employer(id_em, nom_em)

Table1

the second one is
travailler(id_em, id_depart, date_chnge)

Table2

a certain id_em can have multiple entries in the travailler table but I want only to get a table with the latest entry of id_em, so basically the one with the biggest date.

so the result of my query should be something like this :
(id_em, nom_em, id_depart, date_change)

but only one entry for every id_em, the one that has the latest date
I've tried this but it shows all of them, I don't know what's wrong

SELECT employe.nom_em, travailler.id_em, travailler.id_depart, max(travailler.date_chnge) FROM employe INNER JOIN travailler ON employe.id_em=travailler.id_em GROUP BY employe.id_em


Please help!

Answer
SELECT 
     e.nom_em, 
     t.id_em, 
     t.id_depart, 
     t.date_chnge
FROM 
    employe e
INNER JOIN 
     travailler t
ON e.id_em=t.id_em 
WHERE
     t.date_chnge = (select max(tr.date_chnge) from travailler tr where tr.id_em = e.id_em)
Comments