[mrchristoph]

Hi,

I've got a table of user's times on a game. I have the following query to get the top 10 quickest times by users:

SELECT uid , MIN(time) as mintime FROM scores WHERE score = 100 AND tid = $tid GROUP BY uid ORDER BY mintime ASC LIMIT 10

This works fine, but what I also want to do is display the logged in user's rank or position within this table even if they aren't in the top 10, and I can't think of any efficient way to do this without looping through the entire table with PHP (which is a lot more than 10!). Any ideas?

[hodephdesign]

mrchristoph, on 02 February 2012 - 01:47 AM, said:

Hi,

I've got a table of user's times on a game. I have the following query to get the top 10 quickest times by users:

SELECT uid , MIN(time) as mintime FROM scores WHERE score = 100 AND tid = $tid GROUP BY uid ORDER BY mintime ASC LIMIT 10

This works fine, but what I also want to do is display the logged in user's rank or position within this table even if they aren't in the top 10, and I can't think of any efficient way to do this without looping through the entire table with PHP (which is a lot more than 10!). Any ideas?

Been a while since I did something like this, but did it at my last job. I would have used something like "SELECT COUNT(uid) WHERE mintime > $this_users_time".

[Renaissance-Design]

Use

OR uid = $logedin_user

[mrchristoph]

Quote

Been a while since I did something like this, but did it at my last job. I would have used something like "SELECT COUNT(uid) WHERE mintime > $this_users_time".

So I would have to query once to get the user's best time, and then a second query to find all the other records lower than this? Would this work with the grouping by uid, and ordering by the grouped mintime?

Quote

OR uid = $logedin_user

Can you elaborate on this? I don't see how this would work.

Thanks!

[hodephdesign]

mrchristoph, on 03 February 2012 - 04:07 AM, said:

So I would have to query once to get the user's best time, and then a second query to find all the other records lower than this? Would this work with the grouping by uid, and ordering by the grouped mintime?

All records higher, if you want a ranking. I'm not sure what you mean about the grouping!

[Renaissance-Design]

mrchristoph, on 03 February 2012 - 04:07 AM, said:

Can you elaborate on this? I don't see how this would work.

In the WHERE clause - the PHP variable is made up, but I assume you have the uid of the logged in user?