CODEWITHSUNDEEP
sqlsql-servert-sql
mamucz
mamucz

Reputation: 11

SQL The largest number of consecutive values for each value

I have Tabel MatchResults

id | player_win_id
------------------
1  |    1         
2  |    1        
3  |    3        
4  |    1        
5  |    2       
6  |    3
7  |    3
8  |    1
9  |    1
10 |    1

I need to find out for each player ID the highest number of consecutive victories. I use MS SQL Server.

Expected Result

PLAYER_ID | WIN_COUNT
------------------
    1     |    3
    2     |    1 
    3     |    2

Upvotes: 0

Views: 637

Answers (2)

mamucz
mamucz

Reputation: 11

Now I understand the previous comment. The code for my table is:

select player_win_id, max(cnt)
from (select player_win_id, count(*) as cnt
      from (select *,
                   row_number() over (order by id) as seqnum,
                   row_number() over (partition by player_win_id order by id) as seqnum_p
            from MatchResults ) t
      group by player_win_id, (seqnum - seqnum_p)
     ) p
group by player_win_id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is a type of gaps-and-islands problem. One solution uses the difference of row numbers. So, to get all streaks:

select player_win_id, count(*)
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by player_win_id order by id) as seqnum_p
      from MatchResults t
     ) t
group by player_win_id, (seqnum - seqnum_p);

Why this works is a little tricky to explain. But if you look at the results of the subquery, you'll probably see how the difference between the row number values captures adjacent rows with the same player win id.

For the maximum, the simplest is probably just an aggregation query:

select player_win_id, max(cnt)
from (select player_win_id, count(*) as cnt
      from (select t.*,
                   row_number() over (order by id) as seqnum,
                   row_number() over (partition by player_win_id order by id) as seqnum_p
            from MatchResults t
           ) t
      group by player_win_id, (seqnum - seqnum_p)
     ) p
group by player_win_id;

Upvotes: 3

Related Questions