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