用left outer join的方法排序，

create table fruits (
    type varchar(10) not null,
    variety varchar(20) not null,
    primary key(type, variety));

insert into fruits values
('apple', 'gala'),
('apple', 'fuji'),
('apple', 'limbertwig'),
('orange', 'valencia'),
('orange', 'navel'),
('pear', 'bradford'),
('pear', 'bartlett'),
('cherry', 'bing'),
('cherry', 'chelan');

select l.type, l.variety, count(*) as num
from fruits as l
left outer join fruits as r
    on l.type = r.type
    and l.variety >= r.variety
group by l.type, l.variety;

+--------+------------+-----+
| type   | variety    | num |
+--------+------------+-----+
| apple  | fuji       |   1 |
| apple  | gala       |   2 |
| apple  | limbertwig |   3 |
| cherry | bing       |   1 |
| cherry | chelan     |   2 |
| orange | navel      |   1 |
| orange | valencia   |   2 |
| pear   | bartlett   |   1 |
| pear   | bradford   |   2 |
+--------+------------+-----+