Compare rows in the same table

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

General SQL Server Forums

New to SQL Server Programming

Compare rows in the same table

Author

Topic

RobertB
Starting Member

2 Posts

Posted - 2009-07-22 : 14:18:03

Hello to all,
I'm trying to compare two row in this table


`standings` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `schedule_id` int(10) unsigned NOT NULL,
  `team_id` int(11) unsigned NOT NULL default '0',
  `score` int(2) NOT NULL default '0',
  PRIMARY KEY  (`id`)

These are final score of games the schedule column will be repeated twice in every game so two rows in this table will look like this:

id | schedule_id | team_id | score
1 | 1 | 1 | 5
2 | 1 | 2 | 3
3 | 2 | 3 | 2
4 | 2 | 4 | 3
These are two game. What I'm trying to achieved is

schedule_id | team1 | win | loose
1 | 1 | 1 | 0
1 | 2 | 0 | 1
2 | 3 | 0 | 1
2 | 4 | 1 | 0

and so on and maybe calculate percentage of each team.

Thank you all.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-22 : 14:36:16

This is a Microsoft SQL Server forum, but this may help you out

DECLARE @Standings TABLE (id int ,
schedule_id int NOT NULL,
team_id int ,
score int

)

INSERT INTO @Standings

SELECT 1 , 1, 1 , 5 UNION ALL
SELECT 2 , 1, 2, 3 UNION ALL
SELECT 3, 2, 3, 2 UNION ALL
SELECT 4 , 2, 4, 3

SELECT DISTINCT
s1.Schedule_Id,s1.Team_id
,[Win] = CASE WHEN s1.score > s2.score then 1 else 0 end
,[Lose] = CASE WHEN s1.score < s2.score then 1 else 0 end

FROM
@Standings s1
INNER JOIN
@Standings s2
ON
s1.schedule_id = s2.schedule_id
and s1.team_id <> s2.team_id

Jim

RobertB
Starting Member

2 Posts

Posted - 2009-07-22 : 14:50:14

Thanks J for your fast reply I didn't know they were different but it does not work it give me errors.

Thank you again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources