I'm a novice here (that may be too kind) trying to write a Top 25 College Football ranking script. I've got my tables setup and what not and I'm trying to write the query that will display the results.

Here is the working query that will display the team and totalpoints, however, I also need to determine first place votes, which is the second query below. I've tried to merge them every way I can think of, but I just don't know SQL well enough to get it done.

select team,
sum(points) as totalpoints
from rankem_rank
inner join rankem_team
on rankem_team.teamid = rankem_rank.teamid
where weekid = 1
group by
teamid
order by totalpoints DESC

select sum(rank) as firstplace where rank = 1 from rankem_rank

Is there a way to put them together?

Disregard, unless you don't think this is an efficient query:

select team,
sum(case when rank = 1 then rank else 0 end) as firstplace,
sum(points) as totalpoints
from rankem_rank
inner join rankem_team
on rankem_team.teamid = rankem_rank.teamid
where weekid = 1
group by
teamid
order by totalpoints DESC

Okay, I guess I do have a new question. If I wanted to figure out the previous week's rank while figuring the current week's ranking, how would I go about doing that easily? If you go to the link below, you'll see the PVS column. That's what I'm getting at if I'm not clear.

http://sports.espn.go.com/ncf/rankin...ype=2&pollId=1

Is weekid a montonically increasing week counter?

It would help me a whole bunch to see your schema (the CREATE TABLE statement or statements) and two consecutive weeks worth of data for three or more teams.

Pat,

Yes, the week number is monotonically increasing. Thoughout the season there are 16 weekly polls, one in the preseason (1), Weeks 1-14 (2-15) and then the final rankings (16) after Bowls are played.

Here is the structure for the rankem table and the insert statements for 2 weeks of data.

CREATE TABLE `rankem_rank` (
`rankid` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL,
`teamid` smallint(3) unsigned NOT NULL,
`weekid` smallint(2) NOT NULL default '0',
`rank` smallint(2) NOT NULL default '0',
`points` smallint(2) NOT NULL default '0',
PRIMARY KEY (`rankid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `rankem_rank` VALUES(1, 1, 88, 1, 1, 25);
INSERT INTO `rankem_rank` VALUES(2, 1, 34, 1, 2, 24);
INSERT INTO `rankem_rank` VALUES(3, 1, 46, 1, 3, 23);
INSERT INTO `rankem_rank` VALUES(4, 1, 88, 2, 1, 25);
INSERT INTO `rankem_rank` VALUES(5, 1, 46, 2, 2, 24);
INSERT INTO `rankem_rank` VALUES(6, 1, 34, 2, 3, 23);

Here is the team table:

CREATE TABLE `rankem_team` (
`teamid` tinyint(3) unsigned NOT NULL auto_increment,
`team` varchar(255) NOT NULL,
`mascot` varchar(255) NOT NULL,
`conference` varchar(255) NOT NULL,
PRIMARY KEY (`teamid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=121 ;

INSERT INTO `rankem_team` VALUES(34, 'Georgia', 'Bulldogs', 'Southeastern');
INSERT INTO `rankem_team` VALUES(46, 'LSU', 'Tigers', 'Southeastern');
INSERT INTO `rankem_team` VALUES(88, 'Southern California', 'Trojans', 'Pacific-10');

Anyone have any ideas? Below is the URL to what I've current got up and running. Right now I just have previous being displayed as NR.

http://www.goldhelmet.com/rankings.php?week=1