CODEWITHSUNDEEP
phpmysql
Daniel Robinson
Daniel Robinson

Reputation: 653

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

Upvotes: 2

Views: 59

Answers (2)

Vivek Singh
Vivek Singh

Reputation: 2447

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 ...

Upvotes: 2

rhavendc
rhavendc

Reputation: 1013

You can either use MAX():

SELECT ... FROM ... WHERE ... ORDER BY MAX(usercert.usercertEnd)

Or LIMIT:

SELECT ... FROM ... WHERE ... ORDER BY usercert.usercertEnd DESC LIMIT 1

Upvotes: 1

Related Questions