How to sort the GROUP BY part of a query?

[Woersty]

I need a query like:

SELECT name,parent,software_id FROM software WHERE type_id = 9 GROUP BY parent AND FIRST BEFORE GROUP ORDER parent BY sort_id ASC;

A detailled description with screenshots is available here:

http://www.woerstenfeld.de/problem/problem.html

[Jay]

Thanks very much for providing a detailed description of your problem with screen shots etc. Unfortunately I still didn't get exactly what your problem was, but understood the gist of it. The problem is your question was too technical as to what you wanted, and I was getting confused. It's much easier to say "I want a and b ordered by c from table d" than to say "I want skjfhds and duyr ordered by lskd from table sda" (slightly OTT example but you get the point).

I myself have faced many problems with MySQL not ordering things the way I want to. The way I get around them is to load everything into an array, and then manipulate the array. It's complicated to do, but ensures you get exactly the right results with no need for compromise! You can then use the array to output your data.

[Woersty]

An array was the last way i wanted to use. I have a large table and many users and only one PIII 1GHz... If MySQL returns the correct result I have only a little part of the data to process with php after.

I try to explain it better (shortly) next time but it's a complicated problem and I searched a way to say it that you understand it.

[DutchBen]

Code: Select all

SELECT name,parent,software_id FROM software WHERE type_id = 9 GROUP BY parent AND FIRST BEFORE GROUP ORDER parent BY sort_id ASC;


What the GROUP BY component really does in a query is to make litlle groups of rows that have an identical group by component, in this case it will group al rows that have the same parent.

This means you cannot select 'name' or 'software_id' as such because they are part of a group, that is possibly several different values that happen to all be in rows with an identical value in field parent. MySQL doenst know what value to select from this group and therefor gives you an error.
The same actually goes for the column your ordering by, MySQL cant figure out a sort order because ther might be more values in the group.

Now for the solution.....
What you can do is use (some) funtions on your result set.
eg.

Code: Select all

SELECT parent, min(software_id) as minSoft, max(sort_id) as SortIt FROM software WHERE type_id = 9 GROUP BY parent ORDER BY SortIt ASC;


this selects the maximum value of sort_id in that specifik 'parent group' and uses it later to sort your result.

It is also possible to group by more than one fields which goves you groups in groups - a 'name group' inside a 'parent group' for example, you might want to check that out 2.

Hope it helps and hope this was what your looking 8O for

[DutchBen]

..