View Full Version : Mysql in cod2 query question
IzNoGoD
11th January 2013, 11:02
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:
SELECT time_played FROM (SELECT * FROM checkpoint_statistics WHERE cp_id = 1081 ORDER BY time_played DESC LIMIT 10) LIMIT 1
Please help me :)
kung foo man
11th January 2013, 11:05
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
IzNoGoD
11th January 2013, 11:14
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:
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
IzNoGoD
11th January 2013, 11:14
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:
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:
SELECT * FROM checkpoint_statistics WHERE cp_id = 1081 ORDER BY time_played ASC LIMIT 9,1
Mitch
12th January 2013, 18:33
Sweet, i love it. Mysql + CoD = awesome.
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)
IzNoGoD
19th January 2013, 07:24
Currently I'm in need of another query. I got this one:
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?
Mitch
19th January 2013, 11:06
Try a inner join with player table.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.