Get Top 10 based on sum of Top 3 for each 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 2005 Forums

Transact-SQL (2005)

Get Top 10 based on sum of Top 3 for each date

Author

Topic

peelay
Starting Member

3 Posts

Posted - 2009-01-12 : 13:17:33

Hi there,
I'm hoping someone can help me out on this one as I'm just stumped. Running SQL 2005 so if Rank() can help feel free to offer a solution using it.

I have a table with user stats like this:
StatsId (PK)
UserId (FK)
Score (int)
DateCreated

I would like to return a list of the Top 10 users based on total score. The catch is I only want it based on their first 3 score records for each day. A user could have any number of records, entered multiple times per day. So if the user entered 5 times on 12/12/2008 I only want to include the first 3 records when doing the sum for their score and also include any other sum scores they have for other days.

Another solution I wouldn't mind would be how to delete all other records other than a users first three for each day. Since I don't care about entries 4 and up I don't mind deleting them permanently and then running top 10 select after that.

Any help would be greatly appreciated!
Cheers,
Phill

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2009-01-12 : 17:37:43

Do something like this. This will bring the top three per group. Change it to order by date if you like.

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY StatsID) AS 'RowNo',
StatsID, UserID, Score
FROM #Test --(change this to your table name)
)
SELECT StatsID, UserID, Score
FROM CTE
WHERE RowNo <= 3

peelay
Starting Member

3 Posts

Posted - 2009-01-12 : 21:58:58

DevIQ, thanks for the reply. I'm not that familiar with Partition (I've only just seen it in samples in this forum, but have never used it) so if you don't mind can you clarify a bit more?

In my case I want to get only three records for each day as defined by DateCreated. So if I changed your sample to the following, so that it was Partition By the day the record was created instead of by UserID, would that do the trick?

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Dateadd(day, Datediff(day, 0, DateCreated), 0) ORDER BY StatsID) AS 'RowNo',
StatsID, UserID, Score
FROM #Test --(change this to your table name)
)
SELECT StatsID, UserID, Score
FROM CTE
WHERE RowNo <= 3

Thanks again!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-12 : 22:39:15

ROW_NUMBER() OVER (PARTITION BY UserId,Dateadd(day, Datediff(day, 0, DateCreated), 0) ORDER BY DateCreated)

a)Partition by UserId also because you want the first 3 records for each user for each day.
b) Order by DateCreated because you want the first 3 entries for any given day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 00:12:22

[code]SELECT RANK() OVER (ORDER BY Total DESC) AS Rank,
UserId
FROM
(
SELECT UserId,
SUM(Score) AS Total
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY DateCreated) AS Seq,*
FROM YourTable
)t
WHERE Seq<=3
)r[/code]

peelay
Starting Member

3 Posts

Posted - 2009-01-13 : 23:34:35

Just wanted to say thanks for the help. I used a combination of of visakh16's solution with sunitabeck's recommendation to Partition by UserId and Dateadd(day, Datediff(day, 0, DateCreated), 0).

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 23:42:04

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources