Ordering grouped results within GROUP BY clause

[Antivirus]

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?

[Farcaster]

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

[Antivirus]

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

.

[Farcaster]

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.

[Antivirus]

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