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 |
|
Mark_R
Starting Member
1 Post |
 Posted - 2005-01-13 : 11:55:52
|
| Currently I have two tables player and skater_stats. Player hold basic biographical information and skater_stats holds a year-by-year listing of the player's statistics.I'm running a query to do the following: Show me the TOP players who played between 1965 and 2005, played the center position for team X in the Y league. Order this list by the number of games played in a single season descending.The below query works fine, the trouble I run into is trying to run a query to sort the player list by their career games played for team X in league Y.Single Season (works fine)SELECT TOP 10 p.*, s.* FROM (SELECT p.* FROM player p WHERE p.id > 0 AND p.p_position LIKE 'Center%' AND (p.season_start >= 1965 AND p.season_end <= 2005) ORDER BY p.last_name ASC, p.first_name ASC) p, skater_stats s WHERE s.player_id = p.id AND s.stat_type = 1 AND s.team = 'X' AND s.league = 'Y' ORDER BY s.games_played DESCI have tried several variations of the following to no avail:Career stats (not working)SELECT TOP 10 SUM(s.games_played) AS games_played, p.*, s.* FROM (SELECT p.* FROM player p WHERE p.id > 0 AND p.p_position LIKE 'Center%' AND (p.season_start >= 1965 AND p.season_end <= 2005) ORDER BY p.last_name ASC, p.first_name ASC) p, skater_stats s WHERE s.player_id = p.id AND s.stat_type = 1 AND s.team = 'X' AND s.league = 'Y' ORDER BY games_played DESCAny pointers in the right direction would be appreciated.Mark. |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-13 : 13:02:38
|
Which table has the league data? |
 |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-13 : 13:03:06
|
| Sorry I see it now |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-13 : 13:04:23
|
| I think the problem is your cross join with p and s it create a full outeryou need to join the two tables to get you order by clause to work properly. |
|
|
|
|
|
|
Aggregate and Subquery Problems