February 15, 2010 at 7:09 pm
Hello
I need to display the teams standings of a hockey league. Seems pretty easy but it's not. The problem is when teams are tied in the standings.
When they are tied, the best record between the tied teams is used to order the tied teams.
Here is what I have
A "Team_Record" Table win a calculated column "Ranking" = (Win * 2) + tie.
(A win gives 2 points, a tie 1 point and a lost 0 point)
Select * from Team_REcord Order by Ranking Desc -> give me the standings without taking care of ties
I also have a "Team_Record_Opponent" table where I have the record for one team against each other teams. I have 3 calculated columns
- Ranking = (Win * 2) + tie
- Ranking_Max (maximum rank possible) = (WIN+LOST+TIE)* 2 (probably useless)
- ranking_pct [WIN]*(2)+[TIE]) / ((WIN+LOST+TIE)* 2.0)
Here is the 2 table definition and some records in each.
CREATE TABLE [dbo].[TEAM_RECORD](
[ID_TEAM] [int] NOT NULL,
[TEAM_NAME] [varchar](30) NOT NULL,
[WIN] [tinyint] NULL DEFAULT ((0)),
[LOST] [tinyint] NULL DEFAULT ((0)),
[TIE] [tinyint] NULL DEFAULT ((0)),
[RANKING] AS ([WIN]*(2)+[TIE]),
CONSTRAINT [PK_TEAM_RECORD] PRIMARY KEY CLUSTERED
(
[ID_TEAM] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TEAM_RECORD_OPPONENT](
[ID_TEAM] [int] NOT NULL,
[ID_OPPONENT] [int] NOT NULL,
[WIN] [smallint] NOT NULL DEFAULT ((0)),
[LOST] [smallint] NOT NULL DEFAULT ((0)),
[TIE] [smallint] NOT NULL DEFAULT ((0)),
[RANKING] AS ([WIN]*(2)+[TIE]),
[RANKING_MAX] as (WIN+LOST+TIE)* 2,
[RANKING_PCT] as ([WIN]*(2)+[TIE]) / ((WIN+LOST+TIE)* 2.0),
CONSTRAINT [PK_TEAM_RECORD_OPPONENT] PRIMARY KEY CLUSTERED
(
[ID_TEAM] ASC,
[ID_OPPONENT] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TEAM_RECORD_OPPONENT] WITH CHECK ADD CONSTRAINT [FK_TEAM_RECORD_OPPONENT__OPPONENT] FOREIGN KEY([ID_OPPONENT])
REFERENCES [dbo].[TEAM_RECORD] ([ID_TEAM])
GO
ALTER TABLE [dbo].[TEAM_RECORD_OPPONENT] CHECK CONSTRAINT [FK_TEAM_RECORD_OPPONENT__OPPONENT]
GO
ALTER TABLE [dbo].[TEAM_RECORD_OPPONENT] WITH CHECK ADD CONSTRAINT [FK_TEAM_RECORD_OPPONENT__TEAM_RECORD] FOREIGN KEY([ID_TEAM])
REFERENCES [dbo].[TEAM_RECORD] ([ID_TEAM])
GO
ALTER TABLE [dbo].[TEAM_RECORD_OPPONENT] CHECK CONSTRAINT [FK_TEAM_RECORD_OPPONENT__TEAM_RECORD]
INSERT INTO TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(1, 'TeamA', 6,6,0)
INSERT INTO TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(2, 'TeamB', 7,5,0)
INSERT INTO TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(3, 'TeamC', 6,6,0)
INSERT INTO TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(4, 'TeamD', 5,7,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(1,2,1,3,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(1,3,3,1,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(1,4,2,2,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(2,1,3,1,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(2,3,2,2,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(2,4,2,2,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(3,1,1,3,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(3,2,2,2,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(3,4,3,1,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(4,1,2,2,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(4,2,2,2,0)
INSERT INTO TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(4,3,1,3,0)
If I run
Select * from Team_REcord Order by Ranking Desc
I get this standing
2TeamB75014
3TeamC66012
1TeamA66012
4TeamD57010
There is 2 teams with 12 points : TeamC and TEam A. But TeamA has a better record (3 wins, 1 lost) against TeamC so the standing should be
2TeamB75014
1TeamA66012
3TeamC66012
4TeamD57010
How can I do this?
February 16, 2010 at 5:51 am
This solution will work, but will loop the update statement if there are any group of three (or more) teams with the same RANKING. I guess that is a rather unusual case and that I get away with the loop... 😉
-- Set up tables etc..
CREATE TABLE [dbo].[#TEAM_RECORD](
[ID_TEAM] [int] NOT NULL,
[TEAM_NAME] [varchar](30) NOT NULL,
[WIN] [tinyint] NULL DEFAULT ((0)),
[LOST] [tinyint] NULL DEFAULT ((0)),
[TIE] [tinyint] NULL DEFAULT ((0)),
[RANKING] AS ([WIN]*(2)+[TIE]),
CONSTRAINT [PK_TEAM_RECORD] PRIMARY KEY CLUSTERED
(
[ID_TEAM] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[#TEAM_RECORD_OPPONENT](
[ID_TEAM] [int] NOT NULL,
[ID_OPPONENT] [int] NOT NULL,
[WIN] [smallint] NOT NULL DEFAULT ((0)),
[LOST] [smallint] NOT NULL DEFAULT ((0)),
[TIE] [smallint] NOT NULL DEFAULT ((0)),
[RANKING] AS ([WIN]*(2)+[TIE]),
[RANKING_MAX] as (WIN+LOST+TIE)* 2,
[RANKING_PCT] as ([WIN]*(2)+[TIE]) / ((WIN+LOST+TIE)* 2.0),
CONSTRAINT [PK_TEAM_RECORD_OPPONENT] PRIMARY KEY CLUSTERED
(
[ID_TEAM] ASC,
[ID_OPPONENT] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-- Foreign keys doesen´t work with temporary tables...
--ALTER TABLE [dbo].[#TEAM_RECORD_OPPONENT] WITH CHECK ADD CONSTRAINT [FK_TEAM_RECORD_OPPONENT__OPPONENT] FOREIGN KEY([ID_OPPONENT])
--REFERENCES [dbo].[#TEAM_RECORD] ([ID_TEAM])
--GO
--ALTER TABLE [dbo].[#TEAM_RECORD_OPPONENT] CHECK CONSTRAINT [FK_TEAM_RECORD_OPPONENT__OPPONENT]
--GO
--ALTER TABLE [dbo].[#TEAM_RECORD_OPPONENT] WITH CHECK ADD CONSTRAINT [FK_TEAM_RECORD_OPPONENT__TEAM_RECORD] FOREIGN KEY([ID_TEAM])
--REFERENCES [dbo].[#TEAM_RECORD] ([ID_TEAM])
--GO
--ALTER TABLE [dbo].[#TEAM_RECORD_OPPONENT] CHECK CONSTRAINT [FK_TEAM_RECORD_OPPONENT__TEAM_RECORD]
INSERT INTO #TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(1, 'TeamA', 6,6,0)
INSERT INTO #TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(2, 'TeamB', 7,5,0)
INSERT INTO #TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(3, 'TeamC', 6,6,0)
INSERT INTO #TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(4, 'TeamD', 5,7,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(1,2,1,3,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(1,3,3,1,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(1,4,2,2,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(2,1,3,1,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(2,3,2,2,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(2,4,2,2,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(3,1,1,3,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(3,2,2,2,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(3,4,3,1,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(4,1,2,2,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(4,2,2,2,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(4,3,1,3,0)
--- To test three with same RANKING change to (1=1)
IF (1=0)
BEGIN
INSERT INTO #TEAM_RECORD(ID_TEAM, TEAM_NAME, WIN, LOST, TIE)
VALUES(5, 'TeamE', 6,6,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(5,1,3,1,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(5,2,0,5,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(5,3,2,0,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(5,4,1,0,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(1,5,1,3,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(2,5,5,0,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(3,5,0,2,0)
INSERT INTO #TEAM_RECORD_OPPONENT(ID_TEAM, ID_OPPONENT,WIN, LOST, TIE)
VALUES(4,5,0,1,0)
END
-- First, add a "sort" column and let it initially be the the sort order based on "RANKING"
Select *, rank() over (order by Ranking desc) as Sort
into #team_ranking
from #Team_Record
-- Display result (for testing)
select * from #team_ranking order by RANKING
-- Find the pairs of teams with the same RANKING
-- (Limitation: this can not handle if there are more than two teams with the same RANKING)
select min(ID_TEAM) as TEAM1, max(ID_TEAM) as TEAM2
into #found_pairs
from #team_ranking
group by Sort
having count(*) > 1
-- Display result (for testing)
select * from #found_pairs
while (exists (select top 1 1 from #found_pairs))
begin
-- Find out which of the teams in the found pairs that is the "looser" and
-- add Sort-order to them, making them one step after the "winner" team.
-- Since we didn´t use dense_rank(), the position after will be free...
update #team_ranking
set Sort = Sort + 1
where ID_TEAM IN (
select (select TOP 1 CASE WHEN WIN < LOST THEN ID_TEAM ELSE ID_OPPONENT END
from #TEAM_RECORD_OPPONENT
where ID_TEAM = TEAM1 AND ID_OPPONENT = TEAM2) as Looser
from #found_pairs
)
truncate table #found_pairs
-- Find the pairs of teams with the same RANKING
-- (Limitation: this can not handle if there are more than two teams with the same RANKING)
insert into #found_pairs
select min(ID_TEAM) as TEAM1, max(ID_TEAM) as TEAM2
from #team_ranking
group by Sort
having count(*) > 1
END
-- Display result (for testing)
select *
from #team_ranking
order by Sort
-- clean up
drop table #TEAM_RECORD
drop table #TEAM_RECORD_OPPONENT
drop table #team_ranking
drop table #found_pairs
/Markus
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply