Finding single max score grouping by team and week

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)

Finding single max score grouping by team and week

Author

Topic

bogey
Posting Yak Master

166 Posts

Posted - 2012-03-20 : 10:50:12

I'm trying to using the over (partition) in sql and i'm just not getting the results i want. Here is what my data looks like.

LAST_NAME game1 TEAM_NO WEEK_NO
John 192 1 1
dan-G 189 1 1
dan-G 221 1 2
John 178 1 2

I need to find the max game of each team for the week but i need to include the lastname in the results
Results should look like
Last_Name Game1 Team_no Week_no
John 192 1 1
dan-G 221 1 2

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 11:34:51

SELECT * FROM yourTable o WHERE EXISTS (
SELECT * FROM yourTable i WHERE i.TEAM_NO = o.TEAM_NO AND i.WEEK_NO = o.WEEK_NO
GROUP BY i.TEAM_NO, i.WEEK_NO
HAVING o.game1 = MAX(o.game1))

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:29:20

[code]
SELECT Last_Name, Game1, Team_no, Week_no
FROM
(
SELECT Last_Name, Game1, Team_no, Week_no,
row_number() over (partition by WEEK_NO order by game1 DESC)AS Rn
FROM table
)t
WHERE Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bogey
Posting Yak Master

166 Posts

Posted - 2012-03-21 : 09:22:35

Thank everyone- With a little tweaking I was able to get the results I needed from both of your inputs.

The query below works and pulls the necessary data.

SELECT LAST_NAME, Game1, Team_no, Week_no
FROM
(
SELECT X.LAST_NAME, A.Game1, A.Team_no, A.Week_no,
row_number() over (partition by A.TEAM_NO, A.WEEK_NO order by A.game1 DESC)AS Rn
FROM dbo.BWLRHSTW A, dbo.BowlerPI X
WHERE A.BOWLER_NO = X.BOWLER_NO
)t

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-21 : 09:47:43

bowler?

It would help that you post ALL of the requirements next time

In any case congrats and good luck

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources