Record for most games undefeated

[Bakunin]

Does anyone know who holds the record for most naf games undefeated? (did not lose)

[lunchmoney]

Cant look at NAF site whilst at work, but will look later.

Are you talking a streak of wins (and draws)? Or overall? If the latter that is really easy, the former probably not so.

[nazgob]

I'm currently on 4 i think. Does that count?

[Bakunin]

Cant look at NAF site whilst at work, but will look later.

Are you talking a streak of wins (and draws)? Or overall? If the latter that is really easy, the former probably not so.

Yeah im talking about streak of wins and draws.

[Twelfman]

I'm currently on 4 i think. Does that count?

It counts until proven otherwise! Long live Nazgob!

I got 6 wins or draws at Gert V, does that make me king now?

[lunchmoney]

Yeah im talking about streak of wins and draws.

Then I think it will just need brute force. Look at each coach and look at the tourney records.
Rather you than me

[Don__Vito]

Just to set the ball rolling I've found a streak of 15 games unbeaten for Pipey last year. He's currently on a 13 game streak now too.

[straume]

I had 24 matches in a streak in 2015/2016

[lunchmoney]

Just to set the ball rolling I've found a streak of 15 games unbeaten for Pipey last year. He's currently on a 13 game streak now too.

I find the best way to beat pipey is with a stick. And a step ladder.

[lunchmoney]

I think my biggest unbeaten streak is 4....

[Glowworm]

I think my biggest unbeaten streak is 4....

1... And that's not often

[Vanguard]

I don't know what the structure of the NAF database is like, but assume it's on a MySQL platform. I've tested the following query on my OBBLM database and it seems to work. You'll need to alter the sub-query marked *** RESULTS QUERY *** to suit the NAF database. Alternatively, if you can give me some schema info for the NAF site, I'm happy to tailor the query for you.

Code: Select all

-- Set User Variables
SET @c:=0;
SET @w:=0;
SET @d:=0;
SET @l:=0;
SET @u:=0;
-- Get highest streaks
SELECT	coach_id
	   ,coach
	   ,max(win_streak)											HIGHEST_WIN_STREAK
	   ,max(draw_streak)										HIGHEST_DRAW_STREAK
	   ,max(loss_streak)										HIGHEST_LOSS_STREAK
	   ,max(undefeated_streak)									HIGHEST_UNDEFEATED_STREAK
FROM
	-- *** STREAKS QUERY ***
	(
	-- Get results and streak counters
	SELECT	coach_id
		   ,coach
		   ,CASE WHEN coach_id = @c AND match_result = 'W'
					THEN @w:=@w+1
				 WHEN coach_id != @c AND match_result = 'W'
					THEN @w:=1
				 ELSE @w:=0	END									WIN_STREAK
		   ,CASE WHEN coach_id = @c AND match_result = 'D'
					THEN @d:=@d+1
				 WHEN coach_id != @c AND match_result = 'D'
					THEN @d:=1
				 ELSE @d:=0	END									DRAW_STREAK
		   ,CASE WHEN coach_id = @c AND match_result = 'L'
					THEN @l:=@l+1
				 WHEN coach_id != @c AND match_result = 'L'
					THEN @l:=1
				 ELSE @l:=0	END									LOSS_STREAK
		   ,CASE WHEN coach_id = @c AND match_result != 'L'
					THEN @u:=@u+1
				 WHEN coach_id != @c AND match_result != 'L'
					THEN @u:=1
				 ELSE @u:=0	END									UNDEFEATED_STREAK
		   ,@c:=coach_id
	FROM
		-- *** RESULTS QUERY ***
		(
		-- Team One
		SELECT	c.coach_id										COACH_ID
			   ,c.name											COACH
			   ,t.name											TEAM
			   ,m.match_id										MATCH_ID
			   ,m.date_played									MATCH_DATE
			   ,CASE WHEN team1_score > team2_score THEN 'W'
					 WHEN team1_score < team2_score THEN 'L'
					 ELSE 'D' END 								MATCH_RESULT
		FROM matches m
			JOIN teams t
				ON m.team1_id = t.team_id
			JOIN coaches c
				ON t.owned_by_coach_id = c.coach_id
		WHERE date_played IS NOT NULL

		UNION ALL
		-- Team Two
		SELECT	c.coach_id										COACH_ID
			   ,c.name											COACH
			   ,t.name											TEAM
			   ,m.match_id										MATCH_ID
			   ,m.date_played									MATCH_DATE
			   ,CASE WHEN team2_score > team1_score THEN 'W'
					 WHEN team2_score < team1_score THEN 'L'
					 ELSE 'D' END 								MATCH_RESULT
		FROM matches m
			JOIN teams t
				ON m.team2_id = t.team_id
			JOIN coaches c
				ON t.owned_by_coach_id = c.coach_id
		WHERE m.date_played IS NOT NULL
		ORDER BY coach_id ASC, match_date ASC
		) r
	) s
GROUP BY coach_id, coach
ORDER BY highest_win_streak DESC
		,highest_undefeated_streak DESC
		,highest_draw_streak DESC
		,highest_win_streak DESC

[Don__Vito]

I've found an 18 game streak for myself. Quite chuffed with that!

[Darkson]

5-win streak, 8-undefeated streak.

Shocked it's that high!

[Gaixo]

I'm currently at 41 games. And that includes 5 games with halflings.

EDIT: Should anyone ever revisit this thread, it ended at 54.