Ordering grouped results within GROUP BY clause

[Antivirus]

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



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



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:



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------



With:



Code:
---------------
Code is only visible to licensed users, and only when logged into the forums.
---------------




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

[Antivirus]

I posted a much simpler explaination of my issue in this thread (http://www.vbulletin.org/forum/showthread.php?t=151833)...