complex SQL query to order by joined table data

Question

Here is the sample data, boiled down as much as possible:

Table: items (id / manufacturer)
1 / Microsoft
2 / Microsoft
3 / Microsoft
4 / ACME
5 / Microsoft

Table: order_rows (item_id / date)
1 / 2012-12-01
1 / 2013-01-01
2 / 2013-01-02
2 / 2013-01-03
3 / 2013-01-04
3 / 2013-01-05
3 / 2013-01-06

I want a list of all items by Microsoft, ordered by the number of purchases since 2013-01-01.

So, whichever item by Microsoft that has the most entries in order_rows where date > 2013-01-01 will be first. All items with zero purchases since 2013-01-01 will be at the bottom (NOT excluded from list).

Is this possible to accomplish with a single query? Also, will this be too expensive to be practical?

Desired output would be ordered as below: 3, 2, 1, 5

Answer 1

You should be able to use something similar to this which joins the tables and then uses and ORDER BY count(item_id) desc to get the data in the order that you want:

select i.id, i.manufacturer
from items i
left join order_rows o
  on i.id = o.item_id
  and o.date > '2013-01-01'
where i.manufacturer  ='Microsoft'
group by i.id, i.manufacturer
order by count(o.item_id) desc;

See SQL Fiddle with Demo

If you just want the ID, then you can remove the manufacturer from the SELECT and GROUP BY:

select i.id
from items i
left join order_rows o
  on i.id = o.item_id
  and o.date > '2013-01-01'
where i.manufacturer  ='Microsoft'
group by i.id
order by count(o.item_id) desc;

See SQL Fiddle with Demo

Answer 2

In case you have a huge number of rows for the order_rows table, the query may help a little in terms of performance.

SELECT b.id, b.`manufacturer`, a.cnt AS num_orders
  FROM
       (
       SELECT item_id, COUNT(*) AS cnt
         FROM order_rows
        WHERE item_id IN (SELECT id FROM items WHERE manufacturer = 'Microsoft')
          AND date >= '2013-01-01'
        GROUP BY item_id
       ) a
 RIGHT OUTER JOIN items b
    ON a.item_id = b.id
 WHERE b.`manufacturer` = 'Microsoft'
 ORDER BY IFNULL(a.cnt, 0) DESC

This will produce the result as below:

See the online demo of this at SQL Fiddle