Retrieve top 10 scores by unique player/date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Retrieve top 10 scores by unique player/date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-15 : 07:23:56

Doug writes "Good afternoon:

I have a bit of a stumper for you regarding a query I need to write. We have a table set up as follows:

CREATE TABLE [dbo].[log_contest] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[initials] [nvarchar] (3) NOT NULL ,
[score] [int] DEFAULT 0 NOT NULL ,
[round] [int] DEFAULT 0 NOT NULL ,
[email] [nvarchar] (50) DEFAULT '---' NOT NULL ,
[ipaddress] [nvarchar] (20) DEFAULT '0.0.0.0' NOT NULL ,
[create_date] [datetime] DEFAULT GETDATE() NOT NULL
) ON [PRIMARY]

Every time a game is played, a record is written to the table that stores the user's initials, round, score, email address, etc.

I need to write a query that will return to me the score, email address, round and date of the top 10 scores. HOWEVER, it is the top 10 unique scores by email address.

I'm able to do it easily if just score and email address are used:

select l.email, MAX(l.score) as score
from log_contest l
group by l.email
order by l.score desc

but simply can't figure it out once round and create_date are added.

Could you help please?

Doug"

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-15 : 07:34:58

select TOP 10 l.email, MAX(l.score) as score
from log_contest l
group by l.email
order by l.score desc

Take the query you provided then join it back to the original table to identify the whole row. You can do the TOP 10 in the inner or outer query. I put it in the inner query.

Select A.email, A.score, round, create_date
FROM log_contest A
INNER JOIN (
-- INSERT THE QUERY ABOVE HERE
) B ON A.email = B.email and A.score = B.score

I have to get up an hour early to find a post that I can answer. How am I ever going to reach 1000?

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-15 : 07:44:20

Doug take a look at this thread about getting Top N of a group.

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-15 : 09:05:57

SAM -- what you posted does't work -- you can't order by a field that is not grouped by or part of an aggregate expression ...

select TOP 10 l.email, MAX(l.score) as score
from log_contest l
group by l.email
order by l.score desc

Do you mean:

ORDER BY MAX(l.Score)

??

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-15 : 14:16:40

The ORDER BY is wrong Jeff. I took the query in the first post without taking a close look at it.

Jay - I looked at the TOP N thread, although not closely. I'm not sure the problem applies here? In this instance, if a variable TOP X is wanted, I'd just use SET ROWCOUNT.

SET ROWCOUNT @MyRowcount
Select A.email, A.score, round, create_date
FROM log_contest A
INNER JOIN (
select l.email, MAX(l.score) as score
from log_contest l
group by l.email
) B ON A.email = B.email and A.score = B.score
ORDER BY A.score DESC
SET ROWCOUNT 0

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-16 : 07:14:37

Sam, you are giving the top @MyRowCount. I think Doug wants the top @MyRowCount for each email.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources