Count, based on a select. 2

[LaundroMat]

Hi,

Suppose I have these tables:

game: game_id, gamename
score: user_id, game_id, score
user: user_id, username

I would want to get a list of how many top scores each user has. I got as far as:

SELECT score, username, gamename
FROM score, user, game
WHERE score.user_id = user.user_id
AND score.game_id = game.game_id
GROUP BY game.game_id;

This gives me a list of all top scores, and their owners. Can I do a COUNT() of all this, grouped by user in the same statement, or would I have to issue two queries? If so, how do I store the result of the first query in a temporary table (dropped immediately after use)?

 

[r937]

SELECT username, gamename, count(score) as countscores
FROM score, user, game
WHERE score.user_id = user.user_id
AND score.game_id = game.game_id
GROUP BY username, gamename


rudy

http://r937.com/

SQL Consulting

 

[LaundroMat]

Almost there This gives the amount of scores each user has registered.

In fact, I'm looking to see how many 1st places each player has. I've been able to do it with the query I mentioned above, but then afterwards there's some operations to be done on the resulting rows (through arrays and such).

I was simply curious if I would be able to get the amount of no. 1 scores/user with one query.

 

[r937]

okay, misunderstood your requirement

you can't do it all in one query without subqueries

here's a query which lists all the games that each user has the top score of

you would then just count these per user, either in another query, or in your application code

[tt]select username
, s1.score
, gamename
from user
inner
join score s1
on user.user_id = s1.user_id
inner
join game
on s1.game_id = game.game_id
inner
join score s2
on game.game_id = s2.game_id
group
by username
, s1.score
, gamename
having s1.score = max(s2.score) [/tt]

rudy

http://r937.com/

SQL Consulting

 

[vanekl]

To create a temporary table that is automatically deleted
when the database connection is closed:

CREATE TEMPORARY TABLE tbl_name AS SELECT blah blah blah...

 

[LaundroMat]

Well, I don't know HOW it works, but it does the job superbly. Thanks!

/runs off into a corner to study table joins more intensively.