MALON wrote:
> I've never posted any questions about SQLite before, so I don't know what
> information I need to give you about it, so I'll just link you to a copy of
> my SQL database: http://www.fileden.com/files/2007/3/10/869420/climb.sq3
> http://www.fileden.com/files/2007/3/10/869420/climb.sq3
>
> I have taken over maintaining a plugin for a game because the original
> author stopped working on it, and I'd like to add a query to part of the
> source code of the game. This is basically a racing game.
>
> Here's the breakdown of what I'm doing:
>
> Player says "/mytoptimes" and it returns all maps in which they have hold
> the #1 fastest record for and the time associated with it. Example: If
> there are 5 tracks: A, B, C, D, and E, and they hold the record for B and D,
> the query should return B and D, along with the associated times.
>
> Here are the important tables/columns that go with this query (if you can't
> use the DB I provided)
>
> Table 1:
> climb_scores
>
> columns for climb_scores
> user_id (int)(unique identifier)
> map_name (string)(track name)
> fin_time (float)(players finishing time
>
> Table 2:
> climb_players
>
> columns for climb_players
> user_id (int)(same as previous table, useful for joins)
> alias (string)(players recorded name)
> steam_id (string)(unique identifier, explained below)
>
> Every Valve/steam account has a unique number attached to it, the Steam ID.
> When the player says "/mytoptimes", I will pass that players Steam ID to the
> query in the form of a variable so you can get the top times of the specific
> player based on Steam ID. For an example Steam ID, you can use mine:
>
> STEAM_0:1:11718381
>
>
> Everything in the database is non-sensitive information, so don't worry
> about that. Steam ID's are public information.
>
> I hope I've provided enough information.
>
I think this query should do what you are looking for. You will have to
bind a value to the :steam_id parameter and execute the query to get the
results.
select map_name, fin_time
from climb_players as player
join (
select user_id, map_name, fin_time
from climb_scores as score
join (
select map_name as name, min(fin_time) as time
from climb_scores group by map_name
) as fastest
where score.map_name = fastest.name
and score.fin_time = fastest.time
) as records
where player.steam_id = :steam_id
and player.user_id = records.user_id;
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
