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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-22 : 23:15:45
|
greg writes "I know this is long, but I am trying to write a SQL statement (using Sybase) to determine which game a player had the most "At Bats", against a given team.
SIMPLY PUT: Display the records with the most "At Bats" a player had against each team they have played.
I thought I may have to use a subquery, but I can not figure out how to put it all together to get the results I am looking for.
• EXAMPLE DATA:
===============================================
GameID TeamID PlayerID PlayerName AtBats Hits Srikeouts
1 1 1001 Ted Williams 3 2 1
2 1 1001 Ted Wiliiams 4 1 3
3 2 1001 Ted Williams 0 0 0
4 2 1001 Ted Williams 5 3 2
5 2 1001 Ted Williams 3 1 2
NOTE: The above data illustrates that Ted Williams has played 2 teams
during a 5 game span. During the first two games, he played against
team 1, which in game 2, he was at the plate 4 times. During the next
three games, he played against team 2, which in game 4, he was at the
plate 5 times. Each of those two games he was at the plate the most
against the two different teams. With that in mind, here is the results
I was looking for to be accomplised in a SQL statement.
• EXAMPLE RESULTS (using the above data):
===============================================
GameID TeamID PlayerID PlayerName AtBats Hits Srikeouts
2 1 1001 Ted Wiliiams 4 1 3
4 2 1001 Ted Williams 5 3 2
• EXAMPLE SQL STATEMENT (Which I know is wrong)
===============================================
SELECT TeamID, PlayerID, PlayerName, MAX(AtBats), GameID, Hits, Srikeouts
FROM PlayerData
WHERE PlayerID = 1001
GROUP BY TeamID, PlayerID, PlayerName, GameID, Hits, Srikeouts
My problem is grouping GameID, Hits and Strikeouts will not give me
the result I was looking for. Is there a way NOT to include GameID,
Hits and Strikeouts in the GROUP BY?
Once again, any help or suggestions would be greatly appreciated.
Thank you,
Greg" |
|
|
|
|
|
|
|
Need to use MAX without GROUP BY in SQL query