Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Shon Levi
Shon Levi
6,036 Points
Posted by Shon Levi
Shon Levi
6,036 Points

Manipulate SQL table for highscores

Hey you all, Trying to make some game in php-sql

I want to manipulate a table in the db that save all the record and displayed it

What I need to do is - to add record by "score" (numbers) and after that by time (seconds-number)...

At first I made a table with "id - name - score - time" And displayed it like that:

I display the score that way: SELECT * FROM quiz_1_highscore ORDER BY score DESC, timer ASC

And everything works great - BUT - Now I need to display the user is place in the table...

How can I get that???

1 Answer

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

If I understand correctly, you want to display a single particular user's rank by score. The ranking would be one more than the number of users with higher scores:

SELECT COUNT(*) + 1 AS rank FROM quiz_1_highscore
  WHERE score > (SELECT score FROM quiz_1_highscore WHERE id = <MY_USER_ID>);

Of course, you'd replace "<MY_USER_ID>" with the id of the user you want the ranking of.

Here's another version that counts higher scores AND same scores with lower times:

SELECT COUNT(*) + 1 AS rank FROM quiz_1_highscore h
  JOIN quiz_1_highscore u on u.id = <MY_USER_ID>
  WHERE h.score > u.score
     OR (h.score = u.score AND h.time < u.time);

Is that what you were looking for?