|
|
Posted by Roy Harvey on 06/25/07 12:32
I'm not entirely sure I understand the desired results, but you might
try something like this.
SELECT TOP 10
users.user_naam,
users.user_voornaam,
MAX(scores.score_score) as MaxScore,
users.user_email
FROM users
JOIN scores
ON scores.score_userid = users.user_id
GROUP BY users.user_naam,
users.user_voornaam,
users.user_email
ORDER BY MaxScore DESC
Roy Harvey
Beacon Falls, CT
On Mon, 25 Jun 2007 02:59:56 -0700, zuuperman <nils.destoop@gmail.com>
wrote:
>Hi folks
>
>I have a little problem converting from an access database to the SQL
>server
>I need to have a highscore that shows the top 10 scores, grouped by
>email.
>
>In my access db i had this query:
>"SELECT TOP 10 First(users.user_naam) AS FirstOfuser_naam,
>First(users.user_voornaam) AS FirstOfuser_voornaam,
>Max(scores.score_score) AS MaxOfscore_score FROM users, scores WHERE
>scores.score_userid=[users].[user_id] GROUP BY users.user_email ORDER
>BY Max(scores.score_score) DESC"
>
>But in SQL Server, First isn't known, and without it, i get:
>.. is invalid in the select list because it is not contained in either
>an aggregate function or the GROUP BY clause
>
>How can i make it show the top 10 scores, but with no duplicates of
>email.
>ex:
>SELECT users.user_naam, users.user_voornaam,scores.score_score,
>users.user_email FROM users, scores WHERE scores.score_userid =
>users.user_id
>
>this is the query for selecting all
>
>Someone knows a solution?
>
>Thx
Navigation:
[Reply to this message]
|