How can combine rows of a table with sql?

I have a table like this:

Type	rank
A	1
A	2
B	3
A	4
B	5
B	6
B	7
A	8

And i want convert it to this table with sql query:

Type	rank
A	2
B	3
A	4
B	7
A	8

How can I do this with and with out window functions?

Answer

It is possible to do this without window functions, but they are a vast improvement over the kind of workarounds you had to do before mySQL got them. Here is a solution with @variables. I use the variables to increment a bucket by one every time the type changes, then group on the type and the bucket to get the maxRank for each bucket. Here is a fiddle and the query:

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=12bdc5fbd517905e758a8898025da3c9

select `type`, bucket, max(rank) as maxRank from (
  select `type`, rank, 
    @bucket := @bucket + if(`type` = @curr, 0, 1) as bucket,
    @curr := `type`
  from table1
  join (select @curr:='', @bucket:=0) v
  order by rank
) t group by bucket

I create the variables inside a join. You could also declare them outside of the query, like so:

set @curr:='', @bucket:=0;

select `type`, bucket, max(rank) as maxRank from (
  select `type`, rank, 
    @bucket := @bucket + if(`type` = @curr, 0, 1) as bucket,
    @curr := `type`
  from table1
  order by rank
) t group by bucket

Sometimes it’s easier to declare them inside a join, and sometimes as separate statements. It just depends on your use case.

Two things to bear in mind:

1. don’t name your field names the same thing as a reserved word. You can do it, and you can make it work, but it will be a lot easier to just pick a different field name. This is why type is in backticks. mySQL will let you do it without the backticks, depending on the situation, but then sometimes it will fail on you in others and you’ll eat up a lot of time trying to figure out why the same exact query fails in some uses and not others. Plus it’s a little confusing. Just pick something else.

2. session variables are deprecated and subject to removal in future versions of mySQL. I also don’t think they’re ANSI compliant, so porting this to another database probably wouldn’t work.