$this->db->orderby ?

[eluser]kgill[/eluser]
For those who are telling him ORDER BY doesn't work like that, ordinarily you'd be right if we're talking standards but MySQL went rogue and actually allows expressions in the order by clause so it will work but only if you're using MySQL (which somewhat defeats the purpose of bothering to use active record but that's beside the point).

Now that said I tested the following query and it works, so if you're getting errors when you convert it to AR, then AR is screwing it up when it builds the SQL (enable profiling to verify):

Code:

select *
from companies
WHERE area = 'london'
AND category = 'builders'
AND status = 'active'
ORDER BY sub_area=‘brent’ DESC, sub_area desc
limit 1, 10

if you're using any other DB you have to resort to unions and place holder columns:

Code:

select 1, companies.*
from companies
WHERE area = 'london'
AND category = 'builders'
AND status = 'active'
and sub_area = 'brent'
union
select 2, companies.*
from companies
WHERE area = 'london'
AND category = 'builders'
AND status = 'active'
and sub_area <> 'brent'
order by 1, sub_area desc

As for what's faster, using $this->db->query is always going to be faster because you aren't messing around with all the functions calls for constructing the SQL for you but the speed trade off isn't going to be anything you will notice so basically you're pointlessly optimizing.