How do you select latest entry per column1 and per column2?

Question

I'm fairly new to mysql and need a query I just can't figure out. Given a table like so:

emp  cat  date        amt   cum
44   e1   2009-01-01  1     1
44   e2   2009-01-02  2     2
44   e1   2009-01-03  3     4
44   e1   2009-01-07  5     9
44   e7   2009-01-04  5     5
44   e2   2009-01-04  3     5
44   e7   2009-01-05  1     6
55   e7   2009-01-02  2     2
55   e1   2009-01-05  4     4
55   e7   2009-01-03  4     6

I need to select the latest date transaction per 'emp' and per 'cat'. The above table would produce something like:

emp  cat  date        amt   cum
44   e1   2009-01-07  5     9
44   e2   2009-01-04  3     5
44   e7   2009-01-05  1     6
55   e1   2009-01-05  4     4
55   e7   2009-01-03  4     6

I've tried something like:

select * from orders where emp=44 and category='e1' order by date desc limit 1;
select * from orders where emp=44 and category='e2' order by date desc limit 1;

....

but this doesn't feel right. Can anyone point me in the right direction?

Answer 1

This should work, but I haven't tested it.

SELECT orders.* FROM orders
INNER JOIN (
  SELECT emp, cat, MAX(date) date
    FROM orders
    GROUP BY emp, cat
  ) criteria USING (emp, cat, date)

Basically, this uses a subquery to get the latest entry for each emp and cat, then joins that against the original table to get all the data for that order (since you can't GROUP BY amt and cum).

Answer 2

The answer given by @R.Bemrose should work, and here's another trick for comparison:

SELECT o1.*
FROM orders o1
LEFT OUTER JOIN orders o2
 ON (o1.emp = o2.emp AND o1.cat = o2.cat AND o1.date < o2.date)
WHERE o2.emp IS NULL;

This assumes that the columns (emp, cat, date) comprise a candidate key. I.e. there can be only one date for a given pair of emp & cat.