PDA

View Full Version : Mysql in cod2 query question



IzNoGoD
11th January 2013, 12: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, 12: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, 12: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, 12: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, 19: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, 08: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, 12: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