469,917 Members | 1,766 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,917 developers. It's quick & easy.

problem with highscore

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

Jun 25 '07 #1
2 1729
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" <ni**********@gmail.comwrote in message
news:11**********************@o61g2000hsh.googlegr oups.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
Jun 25 '07 #2
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 <ni**********@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
Jun 25 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Sarah | last post: by
117 posts views Thread by Peter Olcott | last post: by
28 posts views Thread by Jon Davis | last post: by
6 posts views Thread by Ammar | last post: by
30 posts views Thread by MaxLindquist | last post: by
21 posts views Thread by cromoglic | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.