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