Daniel Robinson Daniel Robinson - 7 months ago 11
SQL Question

using group by and order by in mysql query correctly

I'm trying to get the latest certificate a user has from the database. I only want to see the latest one and not all the others so i'm using group by and then ordering by the unique id from the main table. Without group by this works perfectly. I see the last certificate uploaded and then all the others below. As soon as I add the group by I see the first certificate ever uploaded which is pointless as it could be from years ago. My query is quite large as i'm drawing in a lot of other information from other tables. Here is my query.....

SELECT
usercert.*,
cert.*,
certCat.certCatName,
certTask.certTaskName ,
certStatus.certStatusName
FROM `usercert`
INNER JOIN cert ON cert.idcert = usercert.idcert
INNER JOIN certCat ON certCat.idcertCat = cert.idcertCat
INNER JOIN certTask ON certTask.idcertTask = usercert.idcertTask
INNER JOIN certStatus ON certStatus.idcertStatus = usercert.idcertStatus
WHERE usercert.iduser=%s GROUP BY usercert.idcert ORDER BY usercert.usercertEnd DESC

Answer
SELECT 
    usercert.*, 
    cert.*,
    certCat.certCatName,
    certTask.certTaskName ,
    certStatus.certStatusName 
    FROM `usercert`
    INNER JOIN cert ON cert.idcert = usercert.idcert
    INNER JOIN certCat ON certCat.idcertCat = cert.idcertCat
    INNER JOIN certTask ON certTask.idcertTask = usercert.idcertTask
    INNER JOIN certStatus ON certStatus.idcertStatus =   usercert.idcertSttus
    WHERE  usercert.iduser=%s ORDER BY usercert.usercertEnd DESC limit 0,1

in this query it will take all the record in descending order it means the last inserted row will come first and the limit 0,1 means it will start from 0 and fetch 1 record that's it ...

Comments