Your expected result doesn't match your example.

If you want the information to return orange,1 yellow,2 black,1 then use SELECT DISTINCT:
SELECT DISTINCT unit, type FROM table;

However once you start including aggregate functions you will always have problems. Also, on a completely different point, don't use reserved words as field names (i.e. "order" and "type") - it isn't good practice and it'll cause problems later on.

My advice - upgrade to MySQL 5 at least, then you can include sub-queries