## Round-Robin Schedule SQL Puzzle

Puzzle: With a single SELECT statement create a schedule of play for a round-robin tournament. The query should work for odd or even number of players. For odd number of players, the player with bye should be listed last for that round.

Expected Results:

4 players:

``` ROUND PAIRING
------ --------
1 1 vs 2
1 3 vs 4
2 1 vs 4
2 2 vs 3
3 3 vs 1
3 4 vs 2
```

OR

```ROUND PAIRING
----- --------
1 1 vs 2
3 vs 4

2 1 vs 4
2 vs 3

3 3 vs 1
4 vs 2
```

5 players:

```  ROUND PAIRING
------ --------
1 5 vs 1
1 4 vs 2
1 3 - bye
2 1 vs 2
2 5 vs 3
2 4 - bye
3 1 vs 4
3 2 vs 3
3 5 - bye
4 2 vs 5
4 3 vs 4
4 1 - bye
5 3 vs 1
5 4 vs 5
5 2 - bye
```

OR

```ROUND PAIRING
----- -------
1 5 vs 1
4 vs 2
3 - bye

2 1 vs 2
5 vs 3
4 - bye

3 1 vs 4
2 vs 3
5 - bye

4 2 vs 5
3 vs 4
1 - bye

5 3 vs 1
4 vs 5
2 - bye
```

6 players:

```ROUND PAIRING
----- --------
1 5 vs 1
1 4 vs 2
1 3 vs 6
2 1 vs 2
2 5 vs 3
2 6 vs 4
3 1 vs 4
3 2 vs 3
3 5 vs 6
4 1 vs 6
4 2 vs 5
4 3 vs 4
5 3 vs 1
5 6 vs 2
5 4 vs 5
```

OR

```ROUND PAIRING
----- -------
1 5 vs 1
4 vs 2
3 vs 6

2 1 vs 2
5 vs 3
6 vs 4

3 1 vs 4
2 vs 3
5 vs 6

4 1 vs 6
2 vs 5
3 vs 4

5 3 vs 1
6 vs 2
4 vs 5
```

Solution (SQL*Plus script):

```accept players prompt "Enter the number of players: "
set pagesize 100
break on "ROUND" skip 1
col pairing for a10

WITH prompt AS (
SELECT &players AS oplayers
FROM dual
), x AS (
SELECT LEVEL n, oplayers,  oplayers + MOD(oplayers,2) AS players
FROM dual, prompt
CONNECT BY LEVEL<=oplayers + MOD(oplayers,2)-1
), w AS (
SELECT a.n AS rnd, 1+MOD(a.n+b.n,a.players-1) AS p,
CASE WHEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)<=a.players/2 THEN ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY a.n, b.n )-1
ELSE a.players-ROW_NUMBER()OVER(PARTITION BY a.n ORDER BY 1)
END AS match_id, a.oplayers
FROM x a, x b
UNION ALL
SELECT n, players AS p, 0 AS match_id, oplayers
FROM x
)
SELECT rnd AS "ROUND",
CASE WHEN MAX(p)>oplayers THEN  MIN(p) || ' - bye '
WHEN MOD(MAX(p)-MIN(p),2)=1 THEN MIN(p) || ' vs ' || MAX(p)
ELSE MAX(p) || ' vs ' || MIN(p)
END AS pairing
FROM w
GROUP BY rnd, match_id, oplayers
ORDER BY 1, CASE WHEN MAX(p)>oplayers THEN oplayers ELSE MIN(p) END;

```

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.