#1
  1. 300lb Bench!
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2001
    Location
    New York
    Posts
    2,350
    Rep Power
    62

    SQL to get highest rated players over time


    So I have a chess website and everyone has a rating. It's stored in a table with the following fields:

    user
    game_id
    rating
    last_modified

    What I'd like to get out of this is a SQL statement that would give me

    user weeks at top spot
    user1 15 weeks
    user2 12 weeks
    user3 9 weeks
    ...

    At the moment, I can't think of a statement that would give me this and I'm thinking I may have to resort to using multiple statements. Even with multiple statements, however, I can't think of a way of doin this. Any help you can give would be greatly appreciated.
    Correspondence chess
    nothingbutchess.com
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    start with a query that gets you the ratings by user by week

    aside: if a user has several games in one week, how is the rating for that week calculated?

    anyhow, once you have that query working, let's see it and i'll help you from there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo