Beta

Slashdot: News for Nerds

×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Uses for self JOIN: 11) winning streak

toby (759) writes | more than 4 years ago

Databases 0

Cluber on #mysql wanted to find the longest winning streak in a series of matches, like this:

mysql> select * from m;
+----+--------+-------+
| id | winner | loser |
+----+--------+-------+
| 29 | A | B |
| 30 | A | C |
| 31 | C | B |
| 32 | B | A |
| 33 | A | D |
| 34 | B | C |
| 35 | C | D |
| 36 | E | D |
+----+--------+-------+
8 rows in set (0.00 sec)

Cluber on #mysql wanted to find the longest winning streak in a series of matches, like this:

mysql> select * from m;
+----+--------+-------+
| id | winner | loser |
+----+--------+-------+
| 29 | A | B |
| 30 | A | C |
| 31 | C | B |
| 32 | B | A |
| 33 | A | D |
| 34 | B | C |
| 35 | C | D |
| 36 | E | D |
+----+--------+-------+
8 rows in set (0.00 sec)

Longest winning streak per user is the longest series of wins by a user before they lose a match. The wins need not be consecutive. Here is a SQL solution:

mysql> select Streak.winner, max(Streak.cnt)
from (
select m0.id, m0.winner, count(Game.id) as cnt
from m m0
join (
select m1.id, min(m2.id) as minid
from m m1
left join m m2 on m2.loser = m1.winner
and m2.id > m1.id
group by m1.id
) Loss on Loss.id = m0.id
join m Game on Game.winner = m0.winner
and Game.id >= m0.id
and (Loss.minid is null
or Game.id < Loss.minid)
group by m0.id
) Streak
group by Streak.winner;

+--------+-----------------+
| winner | max(Streak.cnt) |
+--------+-----------------+
| A | 2 |
| B | 2 |
| C | 1 |
| E | 1 |
+--------+-----------------+
4 rows in set (0.01 sec)

cancel ×

0 comments

Check for New Comments
Slashdot Account

Need an Account?

Forgot your password?

Don't worry, we never post anything without your permission.

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>
Create a Slashdot Account

Loading...