Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

How to sort the GROUP BY part of a query?

Codes here !

Moderators: macek, egami, gesf

How to sort the GROUP BY part of a query?

Postby Woersty » Fri Oct 18, 2002 1:22 pm

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
User avatar
Woersty
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Oct 17, 2002 9:51 am
Location: Berlin

Postby Jay » Fri Oct 18, 2002 2:35 pm

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.
Jay
 

Postby Woersty » Fri Oct 18, 2002 11:57 pm

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. :?
User avatar
Woersty
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Oct 17, 2002 9:51 am
Location: Berlin

Postby DutchBen » Fri Oct 25, 2002 11:22 am

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
New php-forum User
New php-forum User
 
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam

Postby DutchBen » Mon Oct 28, 2002 2:55 am

..
Last edited by DutchBen on Mon Oct 28, 2002 2:56 am, edited 1 time in total.
DutchBen
New php-forum User
New php-forum User
 
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.