group_by and order_by together

[eluser]Nikita[/eluser]
Hi, I'm trying to get the latest rows "grouped" but it seems like when using group by and order by the rows won't order currently?

Example with order only (this works!)

$this->db->order_by('id', 'DESC');

Result

Code:

Array
(
    [0] => Array
        (
            [id] => 3
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => last
            [time_sent] => 1331874924
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

    [1] => Array
        (
            [id] => 2
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => middle
            [time_sent] => 1331874920
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

    [2] => Array
        (
            [id] => 1
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => first
            [time_sent] => 1331874916
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

)

Example with group (also tried order_by after group_by)

$this->db->order_by('id', 'DESC');
$this->db->group_by('message_id');

Code:

Array
(
    [0] => Array
        (
            [id] => 1
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => first
            [time_sent] => 1331874916
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

)

How do i get the latest row from the row? not the first...

[eluser]oliur[/eluser]
It seems to me you do not need group by at all.

Remember, group by is only useful when you are applying some sort of aggregate function such as MIN,MAX,AVG etc.

If I understood you correctly, you want to display all messages by message_id ASC. And within that you want to display the ids in order of DESC correct?

In other words:


message_id = 1
id = 3

message_id = 1
id = 2

message_id = 1
id = 1




message_id = 2
id = 2

message_id = 2
id = 1

message_id = 3
id = 4


message_id = 3
id = 2




What you have to do is use order by on both of these columns.

So, your sql statement would look like

Code:

SELECT * FROM table_name ORDER BY message_id ASC, order_id DESC

In active record it may look something like this

Code:

$this->db->order_by(‘message_id’, ‘ASC’);
$this->db->group_by(‘id’,DESC);

[eluser]Nikita[/eluser]
Well, the problem is, i don't wanna get the same message_id TWICE, thats why i'm using group.

and

Code:

$this->db->order_by('message_id', 'ASC');
$this->db->group_by('id','DESC');

will not sort my columns as i want (with the latest post and not the first)

also tried with,

Code:

$this->db->group_by('message_id','DESC');

[eluser]oliur[/eluser]
Could you post your database table structure and what exactly you are trying to achieve?

I mean show us the final view result that you want and table structure please.

[eluser]Nikita[/eluser]
Well, as I've said before.

The database structure looks just like the array (all rows returned)

Code:

[0] => Array
        (
            [id] => 1
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => first
            [time_sent] => 1331874916
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

1) Some of the rows will have the same MESSAGE_ID
2) I want the LAST id with the same MESSAGE_ID

Example,

Code:

Array
(
    [0] => Array
        (
            [id] => 3
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => last
            [time_sent] => 1331874924
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

    [1] => Array
        (
            [id] => 2
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => middle
            [time_sent] => 1331874920
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

    [2] => Array
        (
            [id] => 1
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => first
            [time_sent] => 1331874916
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )

)

I just want the ID=3 and not the other two, but i can't seem to sort the database while using group_by (check the first post)

[eluser]oliur[/eluser]
try something like this:

Code:

SELECT * FROM `test` order by message_id DESC limit 0,1

see the limit?