Thank. I added the code, unfortunately it didn't fix it.
I googled around and found articles regarding a bug in a certain version of mysql 5
The bug is about a ordering command which is ignored, when group by is used:
A query that has a GROUP BY seems to be ignoring the subsequent ORDER BY clause. This is new as of mysql 5.0.50
Bug seems to be very annoying on wordpress sites. And probably also why your extra group by code above is ignored by the database.
I have this particular mysql version:
Current WordPress version 3.4 (?) The version of the current WordPress installation.
Current WordPress database version 20596 (?) Increments when changes are made to the WordPress DB schema.
Server operating system Linux (?) The operating system currently running on the server.
Current version of PHP PHP 5.2.12 (?) The current version of PHP used on this server.
Required version of PHP PHP 5.2.4 (?) The minimum version of PHP required to run WordPress.
Current version of MySQL MySQL 5.0.51a-community (?) The current version of MySQL used by WordPress.
Required version of MySQL MySQL 5.0 (?) The minimum version of MySQL required to run WordPress.
I tested the order issue on a testserver, and i didnt have the issue there. But the mysql has an earlier version there: mysql 5.0.47
On my live site I removed the exclusion of categories for the main page, and the sorting went back to normal.
The issue is fixed in 5.0.52, but I dont manage the mySQL version on my hosting server so I cant upgrade myself.
I hope it can help other people, since it took me some hours to get to the bottom of this.