Sorting database query results

[eluser]erjakrobaczek[/eluser]
Hi,
I have code wich is taking data to make tag cloud. It takes 50 most often search words and show it to user.

Code:

$this->db->groupby('name');
$this->db->orderby('COUNT(*) desc, name asc');
$this->db->select('name, COUNT(*) as ile');
$query =$this->db->get('search_result', 50);
$i=0;
foreach($query->result() as $item)    // check the smallest count
{    
    if ($i ==0)
    {
        $min_count = $item->ile;            
    }
    else
    {
        if ($min_count > $item->ile)
        {
            $min_count = $item->ile;
        }
    }
    $i++;
}
$cloud ='';
foreach($query->result() as $item)    // change text size in view
{
    for ($i=1;$i<=6;$i++)
    {
        if ($item->ile >= $i* $min_count && $item->ile< ($i+1)* $min_count)
        {
            $item->size = $i-1;
        }
        if ($item->ile >=6)
        {
            $item->size = 6;
        }
    }
    $cloud .= $this->load->view('tag_cloud', $item, True);            
}            
return $cloud;

In 2nd line it is $this->db->orderby('COUNT(*) desc, name asc'). Firstly it was only order by name and it works great... almost, becaouse it taked newest results, not with most number of search. Now I added order by count, but now the result is: firest all with 6, then 5, 4...
What should I do to sort these top 50 results alphabetical?

[eluser]alpar[/eluser]
Get the top 50 from the database $this->db->orderby('COUNT(*) desc) and use uasort() php function.

I was thinking of a way to do this in the database and came out with something... i don't know if it will work but i think that it's better to do this in the database. I don't know how fast would this be o i would be really interested to hear comments

Code:

Select name,cnt FROM (SELECT count(name)as cnt,name FROM your_table_name GROUP BY name ORDER BY cnt DESC LIMIT 50) ORDER BY name;

is there a better way to do his in the database???

[eluser]erjakrobaczek[/eluser]
When I'm trying your query I have an error:

Code:

Every derived table must have its own alias

[eluser]alpar[/eluser]
Yes it has, my fault. Here's a correction

Select name,cnt FROM (SELECT count(name)as cnt,name FROM your_table_name GROUP BY name ORDER BY cnt DESC LIMIT 50) as sub ORDER BY name;

[eluser]fredwu[/eluser]
Sorry read the thread wrong, ignore me. :/