Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

SQL 2005: Find longest sequence of events within a table

jondow (Programmer) (OP)
13 Jul 11 6:11
Hi all,

I have a table of football results which in its most basic form contains columns for team, date and result (Win,Lose).  Can anyone suggest a way to find the longest run of wins/losses for any team in the table without using a cursor?  The longest run should be limited to a single team but I want the longest run of wins/losses from all teams.

Thanks for your help!

Rick

Team    Date    WiLose
A    01/01/2010    Win
A    07/01/2010    Lose
A    13/01/2010    Lose
A    19/01/2010    Lose
A    25/01/2010    Lose
A    31/01/2010    Lose
A    06/02/2010    Win
A    12/02/2010    Lose
A    18/02/2010    Win
B    01/01/2010    Lose
B    07/01/2010    Win
B    13/01/2010    Lose
B    19/01/2010    Win
B    25/01/2010    Win
B    31/01/2010    Win
B    06/02/2010    Win
B    12/02/2010    Lose
B    18/02/2010    Lose
C    01/01/2010    Lose
C    07/01/2010    Lose
C    13/01/2010    Lose
C    19/01/2010    Lose
C    25/01/2010    Lose
C    31/01/2010    Win
C    06/02/2010    Win
C    12/02/2010    Lose
C    18/02/2010    Win
 
simian336 (Programmer)
13 Jul 11 10:25
Not doing something right myself but getting close... but this will make it easier to test.

create table team (
Team char(1),
Date datetime,
WiLose varchar(10))

SET DATEFORMAT dmy

insert into team values ('A', '01/01/2010', 'Win')
insert into team values ('A', '07/01/2010', 'Lose')
insert into team values ('A', '13/01/2010', 'Lose')
insert into team values ('A', '19/01/2010', 'Lose')
insert into team values ('A', '25/01/2010', 'Lose')
insert into team values ('A', '31/01/2010', 'Lose')
insert into team values ('A', '06/02/2010', 'Win')
insert into team values ('A', '12/02/2010', 'Lose')
insert into team values ('A', '18/02/2010', 'Win')
insert into team values ('B', '01/01/2010', 'Lose')
insert into team values ('B', '07/01/2010', 'Win')
insert into team values ('B', '13/01/2010', 'Lose')
insert into team values ('B', '19/01/2010', 'Win')
insert into team values ('B', '25/01/2010', 'Win')
insert into team values ('B', '31/01/2010', 'Win')
insert into team values ('B', '06/02/2010', 'Win')
insert into team values ('B', '12/02/2010', 'Lose')
insert into team values ('B', '18/02/2010', 'Lose')
insert into team values ('C', '01/01/2010', 'Lose')
insert into team values ('C', '07/01/2010', 'Lose')
insert into team values ('C', '13/01/2010', 'Lose')
insert into team values ('C', '19/01/2010', 'Lose')
insert into team values ('C', '25/01/2010', 'Lose')
insert into team values ('C', '31/01/2010', 'Win')
insert into team values ('C', '06/02/2010', 'Win')
insert into team values ('C', '12/02/2010', 'Lose')
insert into team values ('C', '18/02/2010', 'Win')  

SET DATEFORMAT mdy

select *
from team

--Row_number should do it for you but I am not getting the correct result. But someone will find my flaw..

select *,
row_number() over
(
partition by wilose
order by team, date, wilose
) as wlrank
from team
order by team, date, wilose

Then you just take the highest value for each team and win/loss.

Simi
Qik3Coder (Programmer)
13 Jul 11 12:17
This post is based on simians work.

Simian,
Couldn't directly use the row num function. there was nothing to reset the ticker once it found another streak.

Jondow,
You're not getting away from some sort of looping mechanism.
This is not a cursor, and should perform alright.
My queries are based on Simians table structure, but i had to add more data rows to get multiple streaks for the same team.

CODE

Declare @Teams as table(Team char(1),Date datetime,WiLose varchar(10), GameOrder int)
INSERT INTO @Teams
select *, row_number() over (partition by team order by team, [date]) as GameOrder
from team

DECLARE @TeamData as Table(GameEntry int identity(1,1), Team char(1),GameDate datetime, GameOrder int, WinLose varchar(10), Streak int, GamesInStreak int)

INSERT INTO @TeamData
Select td_1.Team, td_1.[Date], td_1.GameOrder, td_1.WiLose,
CASE WHEN td_1.WiLose = td_3.WiLose OR td_1.WiLose = td_2.WiLose THEN 1 ELSE 0 END Streak, 0 GamesInStreak
FROM @Teams td_1
LEFT JOIN  @Teams td_2 on
    td_1.Team = td_2.Team
    AND td_1.GameOrder + 1 = td_2.GameOrder
LEFT JOIN @Teams td_3 on
    td_1.Team = td_3.Team
    AND td_1.GameOrder - 1 = td_3.GameOrder
ORDER BY td_1.Team, td_1.Date

DECLARE @GameEntry int, @MaxGameEntry int, @Team varchar(255), @WinLose varchar(25), @LastWinLose varchar(25), @LastStreak int, @Streak int, @Ticker int
SELECT @MaxGameEntry = max(GameEntry), @GameEntry = 0 FROM @TeamData
WHILE(@GameEntry < @MaxGameEntry)
 BEGIN
    SELECT @GameEntry = GameEntry, @Team = Team,
        @LastStreak = @Streak, @Streak= Streak,
        @LastWinLose = @WinLose, @WinLose = WinLose,
        @Ticker = CASE WHEN @LastWinLose = @WinLose AND @LastStreak = @Streak AND @Streak = 1 THEN @Ticker+1 ELSE 1 END
    FROM @TeamData where GameEntry = @GameEntry+1
    UPDATE @TeamData
    SET GamesInStreak = @Ticker
    WHERE GameEntry = @GameEntry    
 END
 
--Select * from @TeamData
SELECT Team, Max(CASE WHEN WinLose = 'Win' THEN Tick ELSE 0 END) MaxWins, Max(CASE WHEN WinLose = 'Lose' THEN Tick ELSE 0 END) MaxLosses
FROM (Select Team, WinLose, Max(GamesInStreak) Tick FROM @TeamData
    GROUP BY Team, WinLose) as t
GROUP BY Team
ORDER BY Team

Lodlaiden

The doc walks in. The good news:"It's just Grumpy Old Man Syndrome." The bad news:"You're not even 30."

LarrySteele (Programmer)
13 Jul 11 13:39
I don't use SQL Server, so can't test this.  Seems like this, or a variation, might work.

Starting with your record set, use row_number and partition by Team and WiLose.  I would expect you to get something like this:

CODE

Team  Date            WiLose  Seq
A     01/01/2010      Win     1
A     07/01/2010      Lose    2
A     13/01/2010      Lose    2
A     19/01/2010      Lose    2
A     25/01/2010      Lose    2
A     31/01/2010      Lose    2
A     06/02/2010      Win     3
A     12/02/2010      Lose    4
A     18/02/2010      Win     5
B     01/01/2010      Lose    6
B     07/01/2010      Win     7
B     13/01/2010      Lose    8
B     19/01/2010      Win     9
B     25/01/2010      Win     9
B     31/01/2010      Win     9
B     06/02/2010      Win     9
B     12/02/2010      Lose    10
B     18/02/2010      Lose    10
C     01/01/2010      Lose    11
C     07/01/2010      Lose    11
C     13/01/2010      Lose    11
C     19/01/2010      Lose    11
C     25/01/2010      Lose    11
C     31/01/2010      Win     12
C     06/02/2010      Win     12
C     12/02/2010      Lose    13
C     18/02/2010      Win     14

From that, do a count, grouped by Team/WiLose/Seq and you should get something like this (hopefully I don't have copy/paste/counting errors since this was done by hand pc2):

CODE

Team  WinLose Seq   Count(1)
A     Win     1     1
A     Lose    2     5
A     Win     3     1
A     Lose    4     1
A     Win     5     1
B     Lose    6     1
B     Win     7     1
B     Lose    8     1
B     Win     9     4
B     Lose    10    2
B     Lose    10    2
C     Lose    11    5
C     Win     12    2
C     Lose    13    1
C     Win     14    1

Obviously not tested, but seems like the concept would give you what you're looking for - length of the longest win/loss streaks overall and/or by team.

Hope this helps,
Larry
 
Qik3Coder (Programmer)
13 Jul 11 13:47
Larry,
This is what you get (only Team A, for berevity):
A    2010-01-01 00:00:00.000    Win    1
A    2010-01-07 00:00:00.000    Lose    1
A    2010-01-13 00:00:00.000    Lose    2
A    2010-01-19 00:00:00.000    Lose    3
A    2010-01-25 00:00:00.000    Lose    4
A    2010-01-31 00:00:00.000    Lose    5
A    2010-02-06 00:00:00.000    Win    2
A    2010-02-12 00:00:00.000    Lose    6
A    2010-02-18 00:00:00.000    Win    3

The server uses a ticket says this the Xth row with the same TEAM-WiLose value, so that's the number I'm going to use.
The streaks themselves aren't numbered, so there's no way to reset the row_number ticker.

The doc walks in. The good news:"It's just Grumpy Old Man Syndrome." The bad news:"You're not even 30."

simian336 (Programmer)
13 Jul 11 15:16

I am still stuck on why row_number does not work.

Anybody with a detailed explaination would be appreciated.

Simi
Qik3Coder (Programmer)
13 Jul 11 15:35
I got 10 mins...let's see if I can beat gmm on this.

So the purpose of the ROW_Number function is to let you number a series of rows. You can define the "reset point" (PARTION BY) and the order in which the rows are numbered. This order by clause does not have to be the same as your query, allow the rows to return with a non sequential row_number.

You can only segregate the data by defined points.

SQL doesn't visually group data, like we do automatically in our head. If we see 7 apples and 2 oranges on the table in piles of 2+1, 2+1, and 3 we know inherently which is the largest pile. The problem is that SQL only has this data:

Location, FruitType

Select Location, FruitType, ROW_NUMBER() OVER(Partion BY Location, FruitType, Order by Location) row_num

Table Apple (1)
Table Apple (2)
Table Orange(1)

Table Apple (3)
Table Apple (4)
Table Orange(2)

Table Apple (5)
Table Apple (6)
Table Apple (7)


The problem with jondow's data is that there isn't enough data to have a clean "reset point" If the data had come with streak number, then it would have been easy enough to ROW_NUMBER over the Team/Streak# and then to a Max().
There is not pre-defined grouping of a set of wins/losses, so the only fields you have available are the Team and the WIN LOSS value.
You can use this to number the wins and losses to get a clean total count of each, but there is still nothing "grouping" these into "Streaks" of wins or losses.


This is why I added a streak column to identify rows that were part of a Streak. I tried every variant I could come up with the ROW_Number over the Streak and the Team, but I still didn't have any way of saying "This is streak 1" "this is streak 2".

Lodlaiden

 

The doc walks in. The good news:"It's just Grumpy Old Man Syndrome." The bad news:"You're not even 30."

simian336 (Programmer)
13 Jul 11 15:47
Thanks

Simi
gmmastros (Programmer)
13 Jul 11 16:07

Quote:

I got 10 mins...let's see if I can beat gmm on this.

Nice explanation.  Couldn't have said it better myself.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

markros (Programmer)
13 Jul 11 21:08
See this solution

CODE

create table team (
Team char(1),
Date datetime,
WiLose varchar(10))

SET DATEFORMAT dmy

insert into team values ('A', '01/01/2010', 'Win')
insert into team values ('A', '07/01/2010', 'Lose')
insert into team values ('A', '13/01/2010', 'Lose')
insert into team values ('A', '19/01/2010', 'Lose')
insert into team values ('A', '25/01/2010', 'Lose')
insert into team values ('A', '31/01/2010', 'Lose')
insert into team values ('A', '06/02/2010', 'Win')
insert into team values ('A', '12/02/2010', 'Lose')
insert into team values ('A', '18/02/2010', 'Win')
insert into team values ('B', '01/01/2010', 'Lose')
insert into team values ('B', '07/01/2010', 'Win')
insert into team values ('B', '13/01/2010', 'Lose')
insert into team values ('B', '19/01/2010', 'Win')
insert into team values ('B', '25/01/2010', 'Win')
insert into team values ('B', '31/01/2010', 'Win')
insert into team values ('B', '06/02/2010', 'Win')
insert into team values ('B', '12/02/2010', 'Lose')
insert into team values ('B', '18/02/2010', 'Lose')
insert into team values ('C', '01/01/2010', 'Lose')
insert into team values ('C', '07/01/2010', 'Lose')
insert into team values ('C', '13/01/2010', 'Lose')
insert into team values ('C', '19/01/2010', 'Lose')
insert into team values ('C', '25/01/2010', 'Lose')
insert into team values ('C', '31/01/2010', 'Win')
insert into team values ('C', '06/02/2010', 'Win')
insert into team values ('C', '12/02/2010', 'Lose')
insert into team values ('C', '18/02/2010', 'Win')  

SET DATEFORMAT mdy

select *
from team

--Row_number should do it for you but I am not getting the correct result. But someone will find my flaw..

;with cte as (select *,
row_number() over
(
partition by team order by date
) - ROW_NUMBER() over (partition by team, wilose order by date) as [GroupID]
from team),
cte1 as (select team,
SUM(case when WiLose = 'Win' then 1 else 0 end) as [Wins],
SUM(case when WiLose = 'Lose' then 1 else 0 end) as [Loses]
from cte
group by Team, GroupID),
cteLongestWins as (select top 1 with ties * from cte1 order by Wins DESC),
cteLongestLooses as (select top 1 with ties * from cte1 order by Loses DESC)

-- Top number of consecutive wins and loses
select * from cteLongestWins
UNION ALL
SELECT * from cteLongestLooses

Idea is based on the blog post by Plamen Ratchev
http://pratchev.blogspot.com/2010/02/refactoring-ranges.html

PluralSight Learning Library

markros (Programmer)
13 Jul 11 22:42
Or this solution presented in a blog

Find the Winning Streak

PluralSight Learning Library

jondow (Programmer) (OP)
14 Jul 11 4:03
Wow, lots of responses, thank you all!

I'll have a play with the solutions and see where I end up.

Thanks again.

Rick

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close