Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Unanswered: Syntax question: how do I join these?

    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?

  2. #2
    Join Date
    Mar 2008
    Posts
    5
    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

  3. #3
    Join Date
    Mar 2008
    Posts
    5
    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
    Last edited by Svoboda; 03-29-08 at 22:12.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

  5. #5
    Join Date
    Mar 2008
    Posts
    5
    Quote Originally Posted by Pat Phelan
    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');
    Last edited by Svoboda; 03-30-08 at 11:42.

  6. #6
    Join Date
    Mar 2008
    Posts
    5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •