help after count() data

[eluser]newbie boy[/eluser]
hi guys...

i need help, a function or so...

i can get the total numbers of a field on a certain table...

my problem is i need to show on top the one with highest total down to the lowest...

thanks guys...

[eluser]Thorpe Obazee[/eluser]
you'll probably need to use order_by();

Code:

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

[eluser]newbie boy[/eluser]
it's like this


field user group
1 1
2 1
3 1
4 2
5 3
6 3

i can count the total user from a certain group...

but i need to show on top the group with the highest total of user down to the lowest.

[eluser]Thorpe Obazee[/eluser]
[quote author="newbie boy" date="1242048984"]
but i need to show on top the group with the highest total of user down to the lowest.[/quote]

Do you need to sort the group according to the number of members, descending?

If yes, you'll still be using the order_by and probably use sub queries.

[eluser]Michael Wales[/eluser]

Code:

SELECT users_groups.COUNT(`user`) AS total, groups.title FROM users_groups LEFT JOIN groups ON (groups.id = users_groups.group) GROUP BY `group` ORDER BY total DESC

Code:

$this->db->select('COUNT(`user`) AS total', FALSE);
$this->db->join('groups', 'groups.id = users_groups.group');
$this->db->group_by('group');
$this->db->order_by('total DESC');
$this->db->get('users_groups');

Off the top of my head I can't guarantee that the ORDER BY will work (since total is just an alias) and I don't feel like opening Query Browser to test it. This will give you a good starting point though.