shibbir ahmed shibbir ahmed - 3 months ago 8
MySQL Question

How to get data from table using GROUP BY and ORDER BY keyword?

I have following tables with columns :

1) users

user_id username


2) projects

p_id p_name


3) project_status

psdi p_id cdid cid sid short_list res_sent status_date


4) company

cid company_name


5) status

sid status_name status_order is_cv_sent


6) projects_log

pl_id cdid project_name p_id user_id status date_time


Now, in this
projects_log
table there are same
p_id
value exist.

I want to show all unique
p_id
with latest
date_time
.
as DESC order where
pl.cdid = $cdid


I am using following query but can't get the results. It's not showing me all unique
p_id
as
date_time
DESC order :(

$get_log = mysqli_query($link, "SELECT
pl.*,
u.username,
p.p_name,
c.company_name,
s.status_name,
ps.*
FROM projects_log AS pl
LEFT JOIN users AS u ON u.user_id = pl.user_id
LEFT JOIN projects AS p ON p.p_id = pl.p_id
LEFT JOIN project_status AS ps ON ps.p_id = pl.p_id
LEFT JOIN company AS c ON c.cid = ps.cid
LEFT JOIN status AS s ON s.sid = ps.sid
WHERE pl.cdid = '$cdid' GROUP BY pl.p_id
ORDER BY pl.pl_id DESC ");

Answer

Using below query you will able to achieve group by p_id and will able to get values of max date_time row.

$get_log = mysqli_query($link, "SELECT 
    pl.*, 
    u.username, 
    p.p_name, 
    c.company_name, 
    s.status_name, 
    ps.* 
    FROM (
        select MAX(date_time) as MaxDateTime,p_id from projects_log group by p_id
    ) as mpl join projects_log AS pl on mpl.MaxDateTime = pl.date_time and mpl.p_id = pl.p_id
    LEFT JOIN users AS u ON u.user_id =  pl.user_id 
    LEFT JOIN projects AS p ON p.p_id = pl.p_id     
    LEFT JOIN project_status AS ps ON ps.p_id = pl.p_id 
    LEFT JOIN company AS c ON c.cid = ps.cid 
    LEFT JOIN status AS s ON s.sid = ps.sid 
    WHERE pl.cdid = '$cdid'
    ORDER BY pl.pl_id DESC ");