Mysql group by after order by?

Question

There are few question like this, but they are not constructed in a simple way that make the answer accessible to more generic cases. These question were built for specific cases.

Suppose we have a table

id  letter
----------
1   a
2   a
3   b
4   b
5   a

how do we get this result ?

1   a   2
3   b   2
5   a   1

Possible duplicate of [MySQL order by before group by](http://stackoverflow.com/questions/14770671/mysql-order-by-before-group-by) — Nikolay Mihaylov, May 29 '16 at 06:55

score 2 · Answer 1 · answered May 29 '16 at 05:41

basically you need to know when you have a new "row" to group off of. I am ranking them off of the changed letters

select min(id), letter, sum(my_count)
from (
    select 
        id, letter, count(id) as my_count,
        @rank := if (@letter = letter, @rank, @rank + 1) as rank,
        @letter := letter
    from test
    cross join (select @rank := 0, @letter := '') t
    group by id
) t
group by rank;

FIDDLE

Mysql group by after order by?

1 Answers1