Maximum consecutive row in sql

Question

I am very new to SQL. I have two tables related to two player Tennis Game

Table 1 Name: matches # match information

tourney_id  tourney_name    surface tourney_date  mid winnner_id  loser_id  score   
-------------------------------------------------------------------------------------
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  270   201504  201595  "5-7 6-1 6-3"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  271   201514  201426  "6-3 6-4"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  272   201697  211901  "6-2 7-6(4)"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  273   201620  211539  "6-1 6-0"
"2019-0300" "Luxembourg"    "Hard"  "2019-10-14"  274   214981  203564  "6-2 7-6(2)"

Table 2: players #information about the players

playerid  First Name    Last Name   Country
--------------------------------------------
200001     Martina      Hingis        SUI
200002     Mirjana      Lucic         CRO
200003     Justine      Henin         BEL

You can find complete table contents table1-matches

I need to find out which player(s) won the highest number of matches in a row?

I tired this SQL query using window function but could not succeed

select 
    *,
    count(tourney_id) over (partition by winner_id),
    count(tourney_id) over (partition by tourney_date)
from 
    matches
order by 
    tourney_date

Thanks in advance!

Don't tag spam, just tag the RDBMS you are really using, not 3 completely different ones. — Thom A, Oct 07 '21 at 07:47
Seems like a job for CTE as per https://stackoverflow.com/a/26117629/7034621 — orhtej2, Oct 07 '21 at 07:56
The sample data contains no consecutive wins. In any case, what you describe is called the `gaps and islands` problem. You're looking for *islands* of the same `winner_id` value. — Panagiotis Kanavos, Oct 07 '21 at 08:07
Instead of count you should use row_number function : select winnner_id, tourney_date, max(RwNum) as ConsecutiveWins from (select *, ROW_NUMBER() OVER(PARTITION BY winner_id,tourney_date ORDER BY tourney_date) as RwNum from matches)tab group by winnner_id, tourney_date, — Manoj, Oct 07 '21 at 09:06

score 1 · Answer 1 · answered Oct 07 '21 at 10:48

this is a type of gaps-and-islands problem, with the added complication that we first need to unpivot the wins and losses in order to get the start of each island.

We can use LAG to check the previous match for each player. Then a windowed count gives us a number for each group of wins.

WITH PrevValues AS (
    SELECT
      v.status,
      v.id,
      m.tourney_date,
      StartOfGroup = CASE WHEN LAG(v.status, 1, '.')
              OVER (PARTITION BY v.id ORDER BY m.tourney_date) <> v.status THEN 1 END
    FROM matches m
    CROSS APPLY (VALUES
      ('W', winnner_id),
      ('L', loser_id)
    ) v(status, id)
),
Groups AS (
    SELECT *,
      GroupId = COUNT(*) OVER (PARTITION BY v.id
                ORDER BY m.tourney_date ROWS UNBOUNDED PRECEDING)
    FROM PrevValues v
    WHERE v.status = 'W'
),
PerGroup AS (
    SELECT
      v.id,
      TotalWins = COUNT(*)
    FROM Groups g
    GROUP BY
      g.id,
      g.GroupId
)
SELECT
  p.id,
  MaxConsecutiveWins = MAX(TotalWins)
FROM PerGroup p
GROUP BY
  p.id;

Maximum consecutive row in sql

1 Answers1