Desperetly Need HELP! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Desperetly Need HELP!

Author

Topic

bobbobbob
Starting Member

1 Post

Posted - 2013-10-14 : 15:12:31

I am running into some trouble here and I have a deadline quickly approaching. Can you guys help me please?

SO,given the following two database tables below, can you guys help me write a SQL statement that would return a list of names of the top 10 teams sorted in descending order by average player height. Also, you can assume that player height is stored as an integer representing number of inches.

team
--------
id,
name,
league,
division

player
--------
id,
name,
height,
weight,
team_id

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-14 : 15:17:26

Maybe this will get you going:

SELECT 
	*
FROM 
	team
INNER JOIN
	(
		SELECT TOP 10 team_id, AVG(height) as avg_height
		FROM Player
		GROUP BY team_id
		ORDER BY AVG(height) DESC
	) AS t
	ON team.team_id = t.team_id
ORDER BY
	t.avg_height DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-15 : 00:48:12

what if multiple teams have same average height?


SELECT id,
name,
league,
division
FROM
(
SELECT t.*,DENSE_RANK() OVER (PARTITION BY team_id ORDER BY avg_height DESC) AS Rnk
FROM 
	team t
INNER JOIN
	(
		SELECT team_id, AVG(height*1.0) as avg_height
		FROM Player
		GROUP BY team_id
	) AS t1
	ON t1.team_id = t.team_id
)r
WHERE Rnk < = 10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources