Ordering grouped results within GROUP BY clause
 

I'm having a really tough time with the following query:

[SQL]
SELECT scst_task.taskid, scst_task.enddate, scst_task.recurring, scst_fb.fbid, COUNT(scst_fbposts.fbid) AS fbqty, scst_fb.userid,
scst_fbposts.statusid, scst_status.status, scst_status.alertmember, scst_status.css, scst_status.icon
FROM scst_task
LEFT JOIN usergroup ON usergroup.scstugteamid = scst_task.teamid
LEFT JOIN scst_fb ON scst_fb.taskid = scst_task.taskid AND scst_fb.userid = 1
LEFT JOIN scst_fbposts ON scst_fbposts.fbid = scst_fb.fbid
LEFT JOIN scst_status ON scst_status.statusid = scst_fbposts.statusid
WHERE FIND_IN_SET(usergroup.usergroupid, '13,16') > 0
AND scst_task.startdate <= 1183991294 AND scst_task.enddate > 1183991294
GROUP BY scst_task.taskid
HAVING fbqty < scst_task.recurring
ORDER BY scst_team.teamtitle ASC, scst_task.enddate ASC
[/SQL]

The problem i am having is with regards to the order of the scst_fbposts records grouped by the COUNT clause. Currently it's ordering them by whatever order they are stored within the database, however I need them ordered by scst_fbposts.statusid ASC.

I can easily get that accomplished by adding scst_fbposts.statusid to the GROUP BY clause, however problem is that doing so breaks the COUNT clause and records are no longer grouped.

Can someone please help me?

I'm not sure exactly what your goal is with this query and I don't have the product you must have (is this from Project Tools?). I'm actually surprised that MySQL lets this work. I never realized that it didn't use the standard SQL limitations for GROUP BY. According to the documentation available, the extension of the GROUP BY clause that lets you have non-aggregate columns in your query applies to the ORDER BY clause as well, so from what I am reading, it should work.

However, you might try ORDER BY MAX(scst_fbposts.statusid) or by the column number, like this: ORDER BY 7

Thanks for response - it's not fro project tools, but from a custom add-on which I am working on.

Adding MIN(scst_fbposts.statusid) to the SELECT statement gets the lowest result for that column, but the other columns from that table are still out of order, hence why I am trying to order the scst_fbposts records before the grouping.

The two results which are being grouped (without the group by and count) are as follows:

=========================================
| taskid | enddate | recurring | fbqty | fbid | statusid |
=========================================
| 9 | 1185940800 | 3 | 1 | 11 | 8 |
| 9 | 1185940800 | 3 | 1 | 12 | 4 |
=========================================

And when grouped, they are showing like this:
=========================================
| taskid | enddate | recurring | fbqty | fbid | statusid |
=========================================
| 9 | 1185940800 | 3 | 2 | 11 | 8 |
=========================================

However, I want these grouped rows to be sorted first by statusid ASC because i need the end result to be this:
=========================================
| taskid | enddate | recurring | fbqty | fbid | statusid |
=========================================
| 9 | 1185940800 | 3 | 2 | 12 | 4 |
=========================================

.

Okay, try this, replace:

[SQL]LEFT JOIN scst_fb ON scst_fb.taskid = scst_task.taskid AND scst_fb.userid = 1[/SQL]

With:

[SQL]LEFT JOIN (SELECT * FROM scst_fb ORDER BY statusid ASC) scst_fb ON scst_fb.taskid = scst_task.taskid AND scst_fb.userid = 1[/SQL]


Note, that will only work in MySQL 4.1 and above.

I posted a much simpler explaination of my issue in this thread...