Need Help with Making a Query

jample

Hi -

I am having trouble with a query. Can someone assist please?

Heres the basics:

I need to pull several data items from the DB:

Under table 'games' - the field: 'highscorerid'

The value of the highscorerid is the users ID (IE: user # would be 1 for user #1 and 75 for user # 75 ... etc).

On this table, it houses much information and many fields. Game Name, Game Description ... etc. It is a table for an arcade.

Next, we have a different table: 'user' (It houses the database information) - and 1 of those fields is 'username' (note, that the field 'userid' is also located here ... this is the same value as the field highscorerid located above)

Each game has a highscorerid. 1 user can have umpteen highscores. There are 500 games, so there are 500 lines in this table to go through.

What I want to do, is:

Check the data to find the "top 5 users with most highscores"
and associate the user id with the user name, with the output being something like this:

"The top 5 users with the most highscores are:

1st Place : USERID#x: UsersnameX has xx Highscores
2nd Place : USERID#x: UsersnameX has xx Highscores
3rd Place : USERID#x: UsersnameX has xx Highscores
4th Place : USERID#x: UsersnameX has xx Highscores
5th Place : USERID#x: UsersnameX has xx Highscores"

I can make the output via the template, but am lost asto what query should be run (with as little queries as possible) in order to get the data that I need.

Can anyone help me with this? MANY Thanks for any assistance!!

BSTRhino

SQL is really the most amazing language, in most cases, the most complicated questions can be answered in a single query.

Here's my attempt at a query to do what you want. Try it out, you can change the 'TOP 5' part if you want to see a different number of high scores.

SELECT TOP 5 username, userID, numberOfHighScores 
FROM 
 (SELECT highscorerid, COUNT(*) as numberOfHighScores
 FROM games
 GROUP BY highscorerid) highscorers 
 INNER JOIN users ON highscorers.highscorerid = users.userid
ORDER BY numberOfHighScores DESC

If I got the syntax right, this will work in Microsoft SQL Server. I'm not sure about the limitations of MySQL, I know that it has some limitations, probably to do with subqueries, so if that doesn't work, I can try to rewrite the query in a different way to do what you want.

jample

Hi - Thanks for the effort! I ran the query but it errors out.
Thanks for helping me as much as possible.

BSTRhino

Okay, could you tell me what the error was? It'd help if I knew whether the SQL failed because the engine doesn't support it, or because I got some syntax wrong in the last query.

However, here's a different way of doing the query, I hope this one works:

SELECT users.userid, users.username, COUNT(*) AS numberOfHighScores
FROM users INNER JOIN games ON users.userid = games.highscorerid
GROUP BY users.userid, users.username

That one doesn't use a subquery, so it'll probably be okay. Try it out, and tell me what the error is if it doesn't work.