Determine winner from two distinct lines

[John Grenier]

Hi,

I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).

TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
2 2 4
2 3 4
3 3 3
3 1 4
What I want is to create a view that determines the following
information

VIEW teamMatchStatus
cont_id team_id cont_status
1 1 loss
1 2 win
2 2 tie
2 3 tie
3 3 loss
3 1 win

and eventualy calculate the standings

TABLE standings
team_id win tie loss
1 1 0 1
2 1 1 0
3 0 1 1
Anyone can help me out with this?

Regards,

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mischa Sandberg]

create view teamMatchStatus as
select matchResults.co nt_id, matchResults.te am_id,
case sign(team_score - mid)
when 1 then 'win'
when 0 then 'tie'
else 'loss'
end cont_status
from matchResults
join (
select cont_id, avg(team_score) mid
from matchResults
group by cont_id
) matchMids
on matchMids.cont_ id = matchResults.co nt_id

create view teamStandings as
select team_id,
sum(case when cont_status = 'win' then 1 else 0 end) win,
sum(case when cont_status = 'tie' then 1 else 0 end) tie,
sum(case when cont_status = 'loss' then 1 else 0 end) loss,
from teamMatchStatus
group by team_id
"John Grenier" <jg********@hot mail.com> wrote in message
news:40******** **************@ news.newsgroups .ws...

Hi,

I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).

TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
2 2 4
2 3 4
3 3 3
3 1 4
What I want is to create a view that determines the following
information

VIEW teamMatchStatus
cont_id team_id cont_status
1 1 loss
1 2 win
2 2 tie
2 3 tie
3 3 loss
3 1 win

and eventualy calculate the standings

TABLE standings
team_id win tie loss
1 1 0 1
2 1 1 0
3 0 1 1
Anyone can help me out with this?

Regards,

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Gilson]

"John Grenier" <jg********@hot mail.com> wrote in message
news:40******** **************@ news.newsgroups .ws...

Hi,

I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).

TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
2 2 4
2 3 4
3 3 3
3 1 4
What I want is to create a view that determines the following
information

VIEW teamMatchStatus
cont_id team_id cont_status
1 1 loss
1 2 win
2 2 tie
2 3 tie
3 3 loss
3 1 win
CREATE VIEW TeamMatchStatus (contest, team, result)
AS
SELECT R1.cont_id, R1.team_id,
CASE WHEN R1.contest_resu lt = R2.contest_resu lt
THEN 'tie'
WHEN R1.contest_resu lt > R2.contest_resu lt
THEN 'win'
ELSE 'loss'
END
FROM MatchResults AS R1
INNER JOIN
MatchResults AS R2
ON R1.cont_id = R2.cont_id AND
R1.team_id <> R2.team_id
and eventualy calculate the standings

TABLE standings
team_id win tie loss
1 1 0 1
2 1 1 0
3 0 1 1
CREATE VIEW TeamStandings (team, win, loss, tie)
AS
SELECT team,
COUNT(CASE WHEN result = 'win' THEN 1 END),
COUNT(CASE WHEN result = 'loss' THEN 1 END),
COUNT(CASE WHEN result = 'tie' THEN 1 END)
FROM TeamMatchStatus
GROUP BY team

--
JAG
Anyone can help me out with this?

Regards,

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Kass]

John,

Here's a sneaky solution that might be more efficient than the other
suggestions, since it doesn't require a self-join of matchResults. It will
work so long as the contest results are integers from 0 to 99999, but that
restriction can be widened.
CREATE TABLE matchResults (
cont_id int,
team_id int,
contest_result int,
primary key (cont_id, team_id)
)
insert into matchResults values (1,1,3)
insert into matchResults values (1,2,5)
insert into matchResults values (2,2,4)
insert into matchResults values (2,3,4)
insert into matchResults values (3,3,3)
insert into matchResults values (3,1,4)
go

create table Two (
n int
)
insert into Two values(-1)
insert into Two values(1)
go

create view results as
select
cont_id,
case when n = 1 then minx else maxx end/100000 as team_id,
case sign(n*(minx%10 0000 - maxx%100000))
when -1 then 'loss' when 0 then 'tie' else 'win' end as result
from (
select
cont_id,
min(team_id*100 000 + contest_result) as minx,
max(team_id*100 000 + contest_result) as maxx
from matchResults
group by cont_id
) M, Two
go

select * from results
order by cont_id, team_id

select *
from (
select
team_id,
count(case when result = 'win' then 1 end) as Win,
count(case when result = 'tie' then 1 end) as Tie,
count(case when result = 'loss' then 1 end) as Loss
from results
group by team_id
) S
order by Win - Loss desc
go

drop view results
drop table Two
drop table matchResults

-- Steve Kass
-- Drew University
-- Ref: 9EA47FEA-D8EE-47E9-8ED7-555BA50BF93A

John Grenier wrote:

Hi,

I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).

TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
2 2 4
2 3 4
3 3 3
3 1 4
What I want is to create a view that determines the following
information

VIEW teamMatchStatus
cont_id team_id cont_status
1 1 loss
1 2 win
2 2 tie
2 3 tie
3 3 loss
3 1 win

and eventualy calculate the standings

TABLE standings
team_id win tie loss
1 1 0 1
2 1 1 0
3 0 1 1
Anyone can help me out with this?

Regards,

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Isaac Blank]

Very clever indeed. But this business of folding and unfolding is an
overhead, too. I wonder if MSSQL has something similar to analythic
fuctions, so one can write something like this (I am using Oracle syntax):

SELECT cont_id, team_id, CASE WHEN contest_result < highest_score THEN
'loss' WHEN contest_result > lowest_score THEN 'win' ELSE 'tie' END
FROM (
SELECT cont_id, team_id, contest_result,
MAX(contest_res ult) over (PARTITION BY cont_id ) highest_score,
MIN(contest_res ult) over (PARTITION BY cont_id ) lowest_score
FROM matchresults)

"Steve Kass" <sk***@drew.edu > wrote in message
news:CM******** *********@newsr ead1.news.pas.e arthlink.net...

John,

Here's a sneaky solution that might be more efficient than the other
suggestions, since it doesn't require a self-join of matchResults. It will work so long as the contest results are integers from 0 to 99999, but that
restriction can be widened.
CREATE TABLE matchResults (
cont_id int,
team_id int,
contest_result int,
primary key (cont_id, team_id)
)
insert into matchResults values (1,1,3)
insert into matchResults values (1,2,5)
insert into matchResults values (2,2,4)
insert into matchResults values (2,3,4)
insert into matchResults values (3,3,3)
insert into matchResults values (3,1,4)
go

create table Two (
n int
)
insert into Two values(-1)
insert into Two values(1)
go

create view results as
select
cont_id,
case when n = 1 then minx else maxx end/100000 as team_id,
case sign(n*(minx%10 0000 - maxx%100000))
when -1 then 'loss' when 0 then 'tie' else 'win' end as result
from (
select
cont_id,
min(team_id*100 000 + contest_result) as minx,
max(team_id*100 000 + contest_result) as maxx
from matchResults
group by cont_id
) M, Two
go

select * from results
order by cont_id, team_id

select *
from (
select
team_id,
count(case when result = 'win' then 1 end) as Win,
count(case when result = 'tie' then 1 end) as Tie,
count(case when result = 'loss' then 1 end) as Loss
from results
group by team_id
) S
order by Win - Loss desc
go

drop view results
drop table Two
drop table matchResults

-- Steve Kass
-- Drew University
-- Ref: 9EA47FEA-D8EE-47E9-8ED7-555BA50BF93A

John Grenier wrote:

Hi,

I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).

TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
2 2 4
2 3 4
3 3 3
3 1 4
What I want is to create a view that determines the following
information

VIEW teamMatchStatus
cont_id team_id cont_status
1 1 loss
1 2 win
2 2 tie
2 3 tie
3 3 loss
3 1 win

and eventualy calculate the standings

TABLE standings
team_id win tie loss
1 1 0 1
2 1 1 0
3 0 1 1
Anyone can help me out with this?

Regards,

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[--CELKO--]

>> I have to determine the "standing" (WIN - TIE - LOSS) from
confrontations between two teams on a contest. The table matchResults
has fields [sic] cont_id, team_id and contest_result (int). <<

Please learn that a column is nothing whatsoever like a field before
you write any more code.

The basic problem is that your data model is fundamentally wrong. A
match has more attributes -- a date, two teams (not the same) and two
scores (them and us) at least. I will bet that EVERY answer you get
from other people will keep your crappy DDL and kludge such a table
for the Matches together inside a query Reject the kludges and fix
the **real** problem. Engineering heueristic: mop up the water, but
fix the leak.

CREATE TABLE Matches
(march_date DATETIME NOT NULL,
home_team INTEGER NOT NULL
REFERENCES Teams(team_nbr) ,
visitor_team INTEGER NOT NULL
REFERENCES Teams(team_nbr) ,
CHECK (home_team <> visitor_team)
home_score INTEGER NOT NULL,
visitor_score INTEGER NOT NULL);

I have a series of articles coming out shortly on splitting facts over
multiple tables, columns and rows. This is a classic example. Are you
a George Carlin fan? You are doing his baseball scores routine in
SQL!

[Steve Kass]

Isaac,

There is overhead, but with only two values of the folded value
per cont_id, it's probably not relevant here. In a different
situation, indexing the analog of
(cont_id, the computed column team_id*100000 + contest_result)
might help.

Analytic functions aren't in SQL Server 2000, but they have been announced
for SQL Server 2005, and I believe they follow the ANSI standard closely, as
Oracle does. I believe they are working in the public beta of SQL Server
2005 Express Edition, which is available now:

http://lab.msdn.microsoft.com/express/sql/default.aspx

The express edition, which will be free and freely distributable, is the
SQL Server 2005 engine minus some of the full version's rich toolset, and
restricted to smaller scale use: 1 CPU, 1 GB of memory, and a size limit
of 4 GB per database.

SK

Isaac Blank wrote:

Very clever indeed. But this business of folding and unfolding is an
overhead, too. I wonder if MSSQL has something similar to analythic
fuctions, so one can write something like this (I am using Oracle syntax):

SELECT cont_id, team_id, CASE WHEN contest_result < highest_score THEN
'loss' WHEN contest_result > lowest_score THEN 'win' ELSE 'tie' END
FROM (
SELECT cont_id, team_id, contest_result,
MAX(contest_res ult) over (PARTITION BY cont_id ) highest_score,
MIN(contest_res ult) over (PARTITION BY cont_id ) lowest_score
FROM matchresults)

"Steve Kass" <sk***@drew.edu > wrote in message
news:CM******** *********@newsr ead1.news.pas.e arthlink.net...

John,

Here's a sneaky solution that might be more efficient than the other
suggestions , since it doesn't require a self-join of matchResults. It

will

work so long as the contest results are integers from 0 to 99999, but that
restriction can be widened.
CREATE TABLE matchResults (
cont_id int,
team_id int,
contest_result int,
primary key (cont_id, team_id)
)
insert into matchResults values (1,1,3)
insert into matchResults values (1,2,5)
insert into matchResults values (2,2,4)
insert into matchResults values (2,3,4)
insert into matchResults values (3,3,3)
insert into matchResults values (3,1,4)
go

create table Two (
n int
)
insert into Two values(-1)
insert into Two values(1)
go

create view results as
select
cont_id,
case when n = 1 then minx else maxx end/100000 as team_id,
case sign(n*(minx%10 0000 - maxx%100000))
when -1 then 'loss' when 0 then 'tie' else 'win' end as result
from (
select
cont_id,
min(team_id*100 000 + contest_result) as minx,
max(team_id*100 000 + contest_result) as maxx
from matchResults
group by cont_id
) M, Two
go

select * from results
order by cont_id, team_id

select *
from (
select
team_id,
count(case when result = 'win' then 1 end) as Win,
count(case when result = 'tie' then 1 end) as Tie,
count(case when result = 'loss' then 1 end) as Loss
from results
group by team_id
) S
order by Win - Loss desc
go

drop view results
drop table Two
drop table matchResults

-- Steve Kass
-- Drew University
-- Ref: 9EA47FEA-D8EE-47E9-8ED7-555BA50BF93A

John Grenier wrote:

Hi,

I have to determine the "standing" (WIN - TIE - LOSS) from
confrontatio ns between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).

TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
2 2 4
2 3 4
3 3 3
3 1 4
What I want is to create a view that determines the following
informatio n

VIEW teamMatchStatus
cont_id team_id cont_status
1 1 loss
1 2 win
2 2 tie
2 3 tie
3 3 loss
3 1 win

and eventualy calculate the standings

TABLE standings
team_id win tie loss
1 1 0 1
2 1 1 0
3 0 1 1
Anyone can help me out with this?

Regards,

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Isaac Blank]

Steve,

I guess was not clear enough. Of course packing two values into a single
column and ant then unpacking is not a big deal. What I view as overhead
are the steps of assembling the games and then disassembling them back into
original form. Goes against the non-procedural nature of SQL.
"Steve Kass" <sk***@drew.edu > wrote in message
news:Tt******** *********@newsr ead2.news.pas.e arthlink.net...

Isaac,

There is overhead, but with only two values of the folded value
per cont_id, it's probably not relevant here. In a different
situation, indexing the analog of
(cont_id, the computed column team_id*100000 + contest_result)
might help.

Analytic functions aren't in SQL Server 2000, but they have been announced for SQL Server 2005, and I believe they follow the ANSI standard closely, as Oracle does. I believe they are working in the public beta of SQL Server
2005 Express Edition, which is available now:

http://lab.msdn.microsoft.com/express/sql/default.aspx

The express edition, which will be free and freely distributable, is the
SQL Server 2005 engine minus some of the full version's rich toolset, and
restricted to smaller scale use: 1 CPU, 1 GB of memory, and a size limit
of 4 GB per database.

SK

Isaac Blank wrote:

Very clever indeed. But this business of folding and unfolding is an
overhead, too. I wonder if MSSQL has something similar to analythic
fuctions, so one can write something like this (I am using Oracle syntax):
SELECT cont_id, team_id, CASE WHEN contest_result < highest_score THEN
'loss' WHEN contest_result > lowest_score THEN 'win' ELSE 'tie' END
FROM (
SELECT cont_id, team_id, contest_result,
MAX(contest_res ult) over (PARTITION BY cont_id ) highest_score,
MIN(contest_res ult) over (PARTITION BY cont_id ) lowest_score
FROM matchresults)

"Steve Kass" <sk***@drew.edu > wrote in message
news:CM******** *********@newsr ead1.news.pas.e arthlink.net...

John,

Here's a sneaky solution that might be more efficient than the other
suggestions , since it doesn't require a self-join of matchResults. It

will

work so long as the contest results are integers from 0 to 99999, but thatrestriction can be widened.
CREATE TABLE matchResults (
cont_id int,
team_id int,
contest_result int,
primary key (cont_id, team_id)
)
insert into matchResults values (1,1,3)
insert into matchResults values (1,2,5)
insert into matchResults values (2,2,4)
insert into matchResults values (2,3,4)
insert into matchResults values (3,3,3)
insert into matchResults values (3,1,4)
go

create table Two (
n int
)
insert into Two values(-1)
insert into Two values(1)
go

create view results as
select
cont_id,
case when n = 1 then minx else maxx end/100000 as team_id,
case sign(n*(minx%10 0000 - maxx%100000))
when -1 then 'loss' when 0 then 'tie' else 'win' end as result
from (
select
cont_id,
min(team_id*100 000 + contest_result) as minx,
max(team_id*100 000 + contest_result) as maxx
from matchResults
group by cont_id
) M, Two
go

select * from results
order by cont_id, team_id

select *
from (
select
team_id,
count(case when result = 'win' then 1 end) as Win,
count(case when result = 'tie' then 1 end) as Tie,
count(case when result = 'loss' then 1 end) as Loss
from results
group by team_id
) S
order by Win - Loss desc
go

drop view results
drop table Two
drop table matchResults

-- Steve Kass
-- Drew University
-- Ref: 9EA47FEA-D8EE-47E9-8ED7-555BA50BF93A

John Grenier wrote:

Hi,

I have to determine the "standing" (WIN - TIE - LOSS) from
confrontatio ns between two teams on a contest. The table matchResults
has fields cont_id, team_id and contest_result (int).

TABLE matchResults
cont_id team_id contest_result
1 1 3
1 2 5
2 2 4
2 3 4
3 3 3
3 1 4
What I want is to create a view that determines the following
informatio n

VIEW teamMatchStatus
cont_id team_id cont_status
1 1 loss
1 2 win
2 2 tie
2 3 tie
3 3 loss
3 1 win

and eventualy calculate the standings

TABLE standings
team_id win tie loss
1 1 0 1
2 1 1 0
3 0 1 1
Anyone can help me out with this?

Regards,

Jon

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Kass]

Isaac Blank wrote:

Steve,

I guess was not clear enough. Of course packing two values into a single
column and ant then unpacking is not a big deal. What I view as overhead
are the steps of assembling the games and then disassembling them back into
original form. Goes against the non-procedural nature of SQL.
It does indeed, but but sometimes funny-looking shoes fit just fine. ;)

SK


"Steve Kass" <sk***@drew.edu > wrote in message
news:Tt******** *********@newsr ead2.news.pas.e arthlink.net...

Isaac,

There is overhead, but with only two values of the folded value
per cont_id, it's probably not relevant here. In a different
situation, indexing the analog of
(cont_id, the computed column team_id*100000 + contest_result)
might help.

Analytic functions aren't in SQL Server 2000, but they have been

announced

for SQL Server 2005, and I believe they follow the ANSI standard closely,

as

Oracle does. I believe they are working in the public beta of SQL Server
2005 Express Edition, which is available now:

http://lab.msdn.microsoft.com/express/sql/default.aspx

The express edition, which will be free and freely distributable, is the
SQL Server 2005 engine minus some of the full version's rich toolset, and
restricted to smaller scale use: 1 CPU, 1 GB of memory, and a size limit
of 4 GB per database.

SK

Isaac Blank wrote:

Very clever indeed. But this business of folding and unfolding is an
overhead, too. I wonder if MSSQL has something similar to analythic
fuctions, so one can write something like this (I am using Oracle
syntax):
SELECT cont_id, team_id, CASE WHEN contest_result < highest_score THEN
'loss' WHEN contest_result > lowest_score THEN 'win' ELSE 'tie' END
FROM (
SELECT cont_id, team_id, contest_result,
MAX(contest_ result) over (PARTITION BY cont_id ) highest_score,
MIN(contest_ result) over (PARTITION BY cont_id ) lowest_score
FROM matchresults)

"Steve Kass" <sk***@drew.edu > wrote in message
news:CM***** ************@ne wsread1.news.pa s.earthlink.net ...
John,

Here's a sneaky solution that might be more efficient than the other
suggestions , since it doesn't require a self-join of matchResults. It

will
work so long as the contest results are integers from 0 to 99999, but
that
restricti on can be widened.
CREATE TABLE matchResults (
cont_id int,
team_id int,
contest_result int,
primary key (cont_id, team_id)
)
insert into matchResults values (1,1,3)
insert into matchResults values (1,2,5)
insert into matchResults values (2,2,4)
insert into matchResults values (2,3,4)
insert into matchResults values (3,3,3)
insert into matchResults values (3,1,4)
go

create table Two (
n int
)
insert into Two values(-1)
insert into Two values(1)
go

create view results as
select
cont_id,
case when n = 1 then minx else maxx end/100000 as team_id,
case sign(n*(minx%10 0000 - maxx%100000))
when -1 then 'loss' when 0 then 'tie' else 'win' end as result

from (

select
cont_id,
min(team_id*100 000 + contest_result) as minx,
max(team_id*100 000 + contest_result) as maxx
from matchResults
group by cont_id
) M, Two
go

select * from results
order by cont_id, team_id

select *

from (

select
team_id,
count(case when result = 'win' then 1 end) as Win,
count(case when result = 'tie' then 1 end) as Tie,
count(case when result = 'loss' then 1 end) as Loss
from results
group by team_id
) S
order by Win - Loss desc
go

drop view results
drop table Two
drop table matchResults

-- Steve Kass
-- Drew University
-- Ref: 9EA47FEA-D8EE-47E9-8ED7-555BA50BF93A

John Grenier wrote:
>Hi,
>
>I have to determine the "standing" (WIN - TIE - LOSS) from
>confrontat ions between two teams on a contest. The table matchResults
>has fields cont_id, team_id and contest_result (int).
>
>TABLE matchResults
>cont_id team_id contest_result
>1 1 3
>1 2 5
>2 2 4
>2 3 4
>3 3 3
>3 1 4
>
>
>What I want is to create a view that determines the following
>informatio n
>
>VIEW teamMatchStatus
>cont_id team_id cont_status
>1 1 loss
>1 2 win
>2 2 tie
>2 3 tie
>3 3 loss
>3 1 win
>
>and eventualy calculate the standings
>
>TABLE standings
>team_id win tie loss
>1 1 0 1
>2 1 1 0
>3 0 1 1
>
>
>Anyone can help me out with this?
>
>Regards,
>
>Jon
>
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!