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 :)

There is no "SORT BY" in MySQL, instead use "ORDER BY".

Also give the subtable a name with "AS":

Quote:

---

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

---

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

---

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

---

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)

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?

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