SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group by with additional fields		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sbennetts
Starting Member

United Kingdom
2 Posts
Posted - 11/22/2012 :  08:51:03  Show Profile  Reply with Quote
Hi

I have an annoying problem (well lots really, but one directly relating to this forum).

I have a DB recording sporting high-scores and producing reporting and stats. I'm adding to the stats each week they are produced and, now that we have enough data in to warrant it, I'm adding monthly stats.

I'd like to record the monthly highest score and can do so using the following query:

select YEAR(Game_Date) as yr,MONTH(Game_Date) as mnth, MAX(HighScore) as [High Score]
from utScores
inner join utGames on Game_ID = Score_GameID
group by year(Game_Date), MONTH(Game_Date)

There, nice and easy and I can get a list of individual monthly scores by adding a where clause for my player_ID which is part of the utScores table and points to a utPlayers table.

My problem is, I'd like to include which player got the monthly high score. As soon as I try to do that, I get the cannot use in the select statement as it's not in the group by etc etc. Something like:

select Score_PlayerID, YEAR(Game_Date) as yr,MONTH(Game_Date) as mnth, MAX(HighScore) as [High Score]
from utScores
inner join utGames on Game_ID = Score_GameID
group by year(Game_Date), MONTH(Game_Date)


I've tried so many different things including subqueries etc etc without success.

I'm sure it must be (relatively) easy!


Cheers
Simon

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/22/2012 :  10:11:49  Show Profile  Reply with Quote
One way to do what you want to do is to use the RANK function and make the query into a subquery as shown below:
SELECT * FROM 
(
SELECT Score_PlayerID,
       YEAR(Game_Date) AS yr,
       MONTH(Game_Date) AS mnth,
       HighScore,
       RANK() OVER (PARTITION BY YEAR(Game_Date),MONTH(Game_Date) ORDER BY HighScore DESC)
		AS RN 
FROM   utScores
       INNER JOIN utGames
            ON  Game_ID = Score_GameID
) s WHERE RN = 1;
Go to Top of Page

sbennetts
Starting Member

United Kingdom
2 Posts
Posted - 11/23/2012 :  08:59:13  Show Profile  Reply with Quote
Brilliant, that's worked.

Thanks
Simon

Cheers
Simon
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000