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.
Author |
Topic |
sbennetts
Starting Member
2 Posts |
Posted - 2012-11-22 : 08:51:03
|
HiI 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 utScoresinner join utGames on Game_ID = Score_GameIDgroup 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 utScoresinner join utGames on Game_ID = Score_GameIDgroup 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!CheersSimon |
|
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.ThanksSimonCheersSimon |
|
|
|
|
|
|
|