Group by with additional fields

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 2008 Forums

Transact-SQL (2008)

Group by with additional fields

Author

Topic

sbennetts
Starting Member

2 Posts

Posted - 2012-11-22 : 08:51:03

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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-22 : 10:11:49

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;

sbennetts
Starting Member

2 Posts

Posted - 2012-11-23 : 08:59:13

Brilliant, that's worked.

Thanks
Simon

Cheers
Simon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources