Posted by Dan Guzman on 06/25/07 12:19

You might try substituting MIN (or MAX) for FIRST. This will return the
same result as long as user_naam and user_voornaam values are the same for a
given user_email value.

If user_naam and user_voornaam values differ for a given user_email, you'll
need to specify your requirements for which of those values should be
returned. If you really don't care, MIN/MAX will suffice but the values
might be different than the original Access query.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"zuuperman" <nils.destoop@gmail.com> wrote in message
news:1182765596.662953.248560@o61g2000hsh.googlegroups.com...
> 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
>

[Back to original message]