Longest number of wins in a row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Longest number of wins in a row?

Author

Topic

Blastrix
Posting Yak Master

208 Posts

Posted - 2002-12-23 : 23:23:05

This is a bit of a side project, but I have a database for tracking results of sporting games, and am wanting to pull back statistics for the longest number of wins(or ties) in a row over a specified period. Any suggestions on how to do this?

Thanks,
Steve

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-12-24 : 05:30:29

would a database design change be in order?

ie...at the point of recording the result.....update an accessible and suitable counter...."number of results since last different result"

ie
game 1 win, wincount = 1, drawcount = 0, losscount = 0
game 2 win, wincount = 2, drawcount = 0, losscount = 0
game 3 draw,wincount = 0, drawcount = 1, losscount = 0
game 4 draw,wincount = 0, drawcount = 2, losscount = 0
game 5 draw,wincount = 0, drawcount = 3, losscount = 0
game 6 draw,wincount = 0, drawcount = 4, losscount = 0
game 7 draw,wincount = 0, drawcount = 5, losscount = 0
game 8 draw,wincount = 0, drawcount = 6, losscount = 0
game 9 loss,wincount = 0, drawcount = 0, losscount = 1
game 10 draw,wincount = 0, drawcount = 1, losscount = 0

therefore your query would return....2,6,1 as the longest sequences.

Without a database change, your main issue is one of recursively comparing 1 row in a set to another row in a set....(which sounds like cursor-time!!!!....and we know that's not a good route to go down)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-12-24 : 06:13:06

Steve,

First you have to identify the "ordering" or "sequencing" attribute, usaully a date or sequence number. Then the "event" attribute.

1) First find the number of prior "events" per sequence.
2) Then group on the difference between the number of prior events and the sequence, then find the largest Count of the differences.

Have a look at this TSQL..
It will show the "dry spells" and the number of days without rain.

The Set marked "SubQuery" is Part 1) and the outer query is part 2. The important part is the "group by" expression in the outer query. It looks strange with dates but with sequence numbers turns out to be something like "Group by (X-Y)".


create table RainFall (RainDate datetime not null primary key, Rain int not null default(0))
insert RainFall (RainDate, Rain) values ('20010101',5)
insert RainFall (RainDate, Rain) values ('20010102',0)
insert RainFall (RainDate, Rain) values ('20010103',1)
insert RainFall (RainDate, Rain) values ('20010104',1)
insert RainFall (RainDate, Rain) values ('20010105',2)
insert RainFall (RainDate, Rain) values ('20010106',0)
insert RainFall (RainDate, Rain) values ('20010107',0)
insert RainFall (RainDate, Rain) values ('20010108',0)
insert RainFall (RainDate, Rain) values ('20010109',0)
insert RainFall (RainDate, Rain) values ('20010110',14)
insert RainFall (RainDate, Rain) values ('20010111',2)
insert RainFall (RainDate, Rain) values ('20010112',0)
insert RainFall (RainDate, Rain) values ('20010113',0)
insert RainFall (RainDate, Rain) values ('20010114',4)
insert RainFall (RainDate, Rain) values ('20010115',0)
insert RainFall (RainDate, Rain) values ('20010116',0)
insert RainFall (RainDate, Rain) values ('20010117',0)
insert RainFall (RainDate, Rain) values ('20010118',0)
insert RainFall (RainDate, Rain) values ('20010119',0)
insert RainFall (RainDate, Rain) values ('20010120',1)
insert RainFall (RainDate, Rain) values ('20010121',0)
insert RainFall (RainDate, Rain) values ('20010122',0)
insert RainFall (RainDate, Rain) values ('20010123',0)
insert RainFall (RainDate, Rain) values ('20010124',0)
insert RainFall (RainDate, Rain) values ('20010125',0)
insert RainFall (RainDate, Rain) values ('20010126',1)
insert RainFall (RainDate, Rain) values ('20010127',1)
insert RainFall (RainDate, Rain) values ('20010128',1)
insert RainFall (RainDate, Rain) values ('20010129',1)
insert RainFall (RainDate, Rain) values ('20010130',1)
go

Select Min(RainDate) StartDate, Max(RainDate) EndDate, Count(Dateadd(d,-(Seq),RainDate)) Days
from (
Select R.RainDate, Count(RJ.RainDate) Seq from RainFall R
inner join RainFall RJ on RJ.RainDate < R.RainDate and RJ.Rain = 0
where R.Rain = 0
group by R.RainDate) SubQuery
group by Dateadd(d,-(Seq),RainDate)
order by Days desc

HTH

DavidM

"SQL-3 is an abomination.."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-24 : 11:29:04

Let's try to break it up into steps, and build the SQL from the inside out as we go.

First, lets assume you have this table, called "Games":

GameID, Result
1,W
2,W
3,L
4,L
5,W
6,L
7,T
8,W
9,W
10,W
11,L
..etc...

Next, we need a way of GROUPING each group of wins and losses and ties.

What do the first two wins have in common, or the first two losses? It's hard to tell at first, but they have the SAME COUNT OF RECORDS BEFORE THAT DON'T EQUAL THEIR RESULT.

That is, Games 1 and 2 (W's) both have 0 losses and ties before them. Games 3 and 4 (L's) both have 2 wins and ties before. Game 5 has 2 before it, etc. By grouping the result with this value, we can begin to form our "streak" groups:

SELECT
G1.GameID, G1.Result, SUM(CASE WHEN G1.Result <>G2.Result,1,0 END) as StreakGroup
FROM
Games G1
INNER JOIN
Games G2
ON
G2.ID <= G1.ID
GROUP BY G1.GameID, G1.Result

Look at the results, you should see that if you group by Result and StreakGroup, you can take the MIN of GameID and the MAX of gameID to get the start and end ID of that streak.

SELECT Result, StreakGroup, Min(GameID) as StartGame, Max(GameID) as EndGame, COUNT(*) as StreakLength
FROM
(above SQL) Streaks
HAVING COUNT(*) > 1

That resultset shows us all groups of wins, losses and ties where there was more than 1 in a row, as well as the length of the streak. Looking at the results, it is easy to see the longest length for each, but in SQL we need to do more work.

If you only care about the length of each streak and not the start game and end game, we are just about done:

SELECT Result, Max(StreakLength) as StreakLength
FROM (above SQL) A
GROUP BY Result

However, if you want the start and end gameID for each streak, you need to build just a little bit more, by using the previous two "snippets" of SQL:

SELECT A.Result, Max(A.StreakLength), B.StartGame, B.EndGame
FROM (above SQL) A
INNER JOIN
(SQL before that) B
ON A.Result = B.Result and A.StreakLength = B.StreakLength

Our final statement is quite complex, but hopefully you can get some ideas from this. If you don't care about the start and ending games of the streak, it is pretty easy! And, you get not only Wins, but losses or ties or whatever you want. Of course, you can filter the results to show just wins or whatever you like as needed.

This works for most any application. Note that if you have a "TeamID" you wish to group by, just include that field in all joins and add it as a GROUP BY in all queries.

- Jeff

Edited by - jsmith8858 on 01/10/2003 19:57:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources