CODEWITHSUNDEEP
mysql
John Siniger
John Siniger

Reputation: 885

MySQL Order results before GROUP BY

Hello I would like to know how to perform ORDER BY on the following QUERY: 

SELECT t.*, td.id, td.taskname, td.status, td.date, td.priority,
        DATE_FORMAT(td.completed_on,'%d %b %Y') as completed_on,
        td.completed_by, td.id as idmain,  m.name, c.name as compname,
        GROUP_CONCAT(s.name SEPARATOR '   ') as names
FROM task_assigns t
    LEFT JOIN tasks_todo td ON t.taskid = td.id 
    LEFT JOIN members m ON t.userid = m.id
    LEFT JOIN members s ON t.task_assign = s.id
    LEFT JOIN members c ON td.completed_by = c.id           
WHERE (   t.task_assign = '$userid_task' 
       OR t.userid = '$userid_task' ) 
AND t.date  < NOW() - INTERVAL 10 DAY 
AND td.status = 0  
GROUP BY t.taskid 
LIMIT 10

Right now the returned results are in random order I would like to return them always ordered by id ACS of taskid

enter image description here

Upvotes: 0

Views: 31

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94662

The order of the syntax is 

GROUP BY t.taskid 
ORDER BY t.taskid, other_column_if_you_like
LIMIT 10

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

Simply add the two columns in the ORDER BY clause.

Also, this type of grouping will work only if ONLY_FULL_GROUP_BY is disabled and may produce unexpected results.

select t.*,
    td.id,
    td.taskname,
    td.status,
    td.date,
    td.priority,
    DATE_FORMAT(td.completed_on, '%d %b %Y') as completed_on,
    td.completed_by,
    td.id as idmain,
    m.name,
    c.name as compname,
    GROUP_CONCAT(s.name SEPARATOR '&nbsp;&nbsp; ') as names
from task_assigns t
left join tasks_todo td on t.taskid = td.id
left join members m on t.userid = m.id
left join members s on t.task_assign = s.id
left join members c on td.completed_by = c.id
where (
        t.task_assign = '$userid_task'
        or t.userid = '$userid_task'
        )
    and t.date < NOW() - INTERVAL 10 DAY
    and td.status = 0
group by t.taskid
order by t.id,
    t.taskid LIMIT 10

Upvotes: 1

Related Questions