order by count when you have to count only certain items

I have this query:

SELECT tips.* 
FROM   `tips` `t` 
       LEFT JOIN tip_usage 
         ON tip_usage.tip_id = t.id 
GROUP  BY t.id 
ORDER  BY COUNT(CASE 
                  WHEN status = 'Active' THEN status 
                  ELSE NULL 
                END) DESC 

As you see here, I use a left join and I count onl开发者_运维问答y the records which are Active

Can I make this query to be different, and to lose the case stuff from the count?

If you want to return all tips, regardless of status, and but sort by number of Active records, then this is as pretty as you are going to get.

If you only want to return active tips, then you can add Where status = 'Active' and then just order by Count(t.id) desc.

One alternative is that you have a NumActive int column in the tips table, and you keep this update whenever a new tip_usage record is added or modified for a given tip. This puts more overhead into the insert/delete/update operations for tip_usage, but would make this query much simpler:

select *
from tips
Order by tips.NumActive desc

Another alternative is:

Select tips.*
From tips
Order by (
  Select count(tip_id) 
  From tips_usage as t 
  Where t.tip_id = tips.id and status = 'Active') DESC

Though this exchanges a case for a subquery, so just complex in a different way.

Quick note, you cannot select t.* and group on t.id. So with that being said:

SELECT t.id,coalesce(tu.cntUsed,0) as cntUsed
  FROM   `tips` `t` 
  LEFT 
  JOIN (Select tip_id,count(*) as cntUsed
          from tip_usage
         WHERE status='Active'
         group by tip_id
       ) tu
    ON t.id = tu.tip_id
 ORDER coalesce(tu.cntUsed,0)

Since you want to left-join and include the tips that have no usage, this at least sorts them all at the top with a value of zero, which is the most accurate statement of the reality of what is in the tables.

SELECT tips.*, COUNT(*) AS number
FROM tip_usage
LEFT JOIN tips ON tips.id = tip_id
WHERE STATUS = "Active"
GROUP BY tip_id
ORDER BY number DESC