using $this->db->group_by() and $this->db->order_by() together

[eluser]chefnelone[/eluser]
Hello,

I can't make to use $this->db->group_by() and $this->db->order_by() together

something like:

Code:

$this->db->group_by('id');
        $this->db->order_by("order", "asc");

I need it to groupep but also ordered by 'order'.

What can I do?

[eluser]mddd[/eluser]
Why not? You can use GROUP BY and ORDER BY together just fine.
What exactly is it that doesn't work?

[eluser]danmontgomery[/eluser]
You're probably getting a "column XXX is ambiguous" error (are you joining more than one table with an "id" or "order" field?), and need to specify the table.

[eluser]chefnelone[/eluser]
I have a table like:

id -- group -- colour -- order
1 -- 1 -- black -- 0
2 -- 2 -- blue -- 2
3 -- 2 -- yellow -- 1

then I run the query:

Code:

$this->db->group_by('group');
$this->db->order_by("order", "asc");


$data = $this->db->get('my_table');
$data = $data->result();

If I use $this->firephp->log( $data ); I get this array:

Code:

array(
[0] => stdClass(
id = 1
group = 1
colour = 'black'
orden = 0

)
[1] => stdClass(
id = 2
group = 2
colour = 'black'
orden = 2

)
)

But I need it to be (because order of 'yellow' is lower than 'blue')

Code:

array(
[0] => stdClass(
id = 1
group = 1
colour = 'black'
orden = 0

)
[1] => stdClass(
id = 3
group = 2
colour = 'yellow'
orden = 1

)
)

[eluser]danmontgomery[/eluser]
When the query is executed "group by" is executed before "order by", so you're not going to get the result you expect with a simple query like that.

[eluser]chefnelone[/eluser]
How should I do this then?

[eluser]mddd[/eluser]
When using GROUP BY, you order the results that you get after GROUP BY. There is no way to influence which row you get inside each group; it is simply the first row (or if you have a WHERE clause, the first row to match the WHERE conditions).

However, you could do the sorting first in a subquery. And then group the results in the main query.

Code:

SELECT * FROM (SELECT * FROM table ORDER BY order) AS my_subquery GROUP BY group ORDER BY group

Basically, you are now doing the same query, only you are using a sorted table as the starting point, in stead of the table as it exists in the database.

[eluser]WanWizard[/eluser]
If you group on a certain field, there will be one record in the result set for every value of that field found. The value of all other fields are random, and can not be predicted.
Ordering is something that happens on the resultset, not before. So by the time you order on 'colour', grouping has already happened, and the colour and order field in the resultset are random (could be yellow, could be blue).

[eluser]chefnelone[/eluser]
[quote author="mddd" date="1279134614"]When using GROUP BY, you order the results that you get after GROUP BY. There is no way to influence which row you get inside each group; it is simply the first row (or if you have a WHERE clause, the first row to match the WHERE conditions).

However, you could do the sorting first in a subquery. And then group the results in the main query.

Code:

SELECT * FROM (SELECT * FROM table ORDER BY order) AS my_subquery GROUP BY group ORDER BY group

Basically, you are now doing the same query, only you are using a sorted table as the starting point, in stead of the table as it exists in the database.[/quote]
it's clear.
thanks