Results 1 to 7 of 7

Thread: Mysql in cod2 query question

  1. #1
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts

    Mysql in cod2 query question

    Hey guys,

    Im currently developing a jumpmod with mysql support (thx to kung for porting mysql to cod2) and am in need of a certain query.

    What i have: A table filled with statistics for a certain checkpoint (checkpoint_statistics)
    What i want: the 10th time_played from that table for a certain cp_id

    What i think might work, but doesnt:

    Code:
    SELECT time_played FROM (SELECT * FROM checkpoint_statistics WHERE cp_id = 1081 ORDER BY time_played DESC LIMIT 10) LIMIT 1
    Please help me
    Last edited by IzNoGoD; 11th January 2013 at 12:07.

  2. #2
    Assadministrator kung foo man's Avatar
    Join Date
    Jun 2012
    Location
    trailerpark
    Posts
    2,010
    Thanks
    2,102
    Thanked 1,084 Times in 753 Posts
    There is no "SORT BY" in MySQL, instead use "ORDER BY".

    Also give the subtable a name with "AS":

    SELECT
    time_played
    FROM
    (SELECT * FROM checkpoint_statistics WHERE cp_id = 1081 ORDER BY time_played DESC LIMIT 10) AS tmp
    LIMIT 1
    timescale 0.01

  3. The Following User Says Thank You to kung foo man For This Useful Post:

    IzNoGoD (11th January 2013)

  4. #3
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    Thx for the help

    It was not exactly what i wanted (asc/desc and double-limit hurtz my brainz), but this is what i actually wanted:

    Code:
    SELECT * from (SELECT * FROM (SELECT * FROM checkpoint_statistics WHERE cp_id = 1081 ORDER BY time_played ASC LIMIT 10) AS tmp ORDER BY time_played DESC) as tmp2 LIMIT 1

  5. #4
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    Thx for the help

    It was not exactly what i wanted (asc/desc and double-limit hurtz my brainz), but this is what i actually wanted:

    Code:
    SELECT * from (SELECT * FROM (SELECT * FROM checkpoint_statistics WHERE cp_id = 1081 ORDER BY time_played ASC LIMIT 10) AS tmp ORDER BY time_played DESC) as tmp2 LIMIT 1
    Edit: this also works and is less code:

    Code:
     SELECT * FROM checkpoint_statistics WHERE cp_id = 1081 ORDER BY time_played ASC LIMIT 9,1
    Last edited by IzNoGoD; 11th January 2013 at 12:24.

  6. The Following User Says Thank You to IzNoGoD For This Useful Post:

    kung foo man (11th January 2013)

  7. #5
    Global Mossaderator Mitch's Avatar
    Join Date
    Nov 2012
    Posts
    654
    Thanks
    204
    Thanked 450 Times in 305 Posts
    Sweet, i love it. Mysql + CoD = awesome.

    Code:
    query = "SELECT u.name, u.rank, u.kills, u.deaths, m.moderator FROM users u INNER JOIN moderator m ON u.guid = m.guid WHERE guid = " + self getGuid();
    (or you put all the data into one table, more null or false)

  8. The Following User Says Thank You to Mitch For This Useful Post:

    kung foo man (12th January 2013)

  9. #6
    Assadministrator IzNoGoD's Avatar
    Join Date
    Aug 2012
    Posts
    1,718
    Thanks
    17
    Thanked 1,068 Times in 674 Posts
    Currently I'm in need of another query. I got this one:

    Code:
    select player_id, MIN(time_played) from checkpoint_statistics WHERE cp_id = 1071 GROUP BY player_id ORDER BY MIN(time_played) LIMIT 10
    It returns the top 10 players for a certain checkpoint {id, time_played} and i'd like to link said id to a playername in another table

    Any suggestions?

  10. #7
    Global Mossaderator Mitch's Avatar
    Join Date
    Nov 2012
    Posts
    654
    Thanks
    204
    Thanked 450 Times in 305 Posts
    Try a inner join with player table.


    Code:
    select c.player_id, p.name, MIN(c.time_played) from checkpoint_statistics c INNER JOIN player p ON c.player_id = p.id  WHERE cp_id = 1071 GROUP BY c.player_id ORDER BY MIN(c.time_played) LIMIT 10
    Edit: might some editing

  11. The Following User Says Thank You to Mitch For This Useful Post:

    kung foo man (19th January 2013)

Posting Permissions

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