17. Highest Peak and Longest River by Country

--17

SELECT TOP (5) P.CountryName, P.Elevation AS HighestPeakElevation
, p.LENGTH AS LongestRiverLength
FROM
(
SELECT c.CountryName,p.PeakName, p.Elevation ,

DENSE_RANK() OVER(partition BY c.CountryName ORDER BY
p.Elevation DESC)
AS Rank,
R.RiverName,R.LENGTH ,

DENSE_RANK() OVER(partition BY c.CountryName ORDER BY r.LENGTH DESC)
AS Rankr
FROM Countries c
LEFT JOIN MountainsCountries mc ON C.CountryCode=
mc.CountryCode.
LEFT JOIN Mountains m ON m.id= mc.MountainId
LEFT JOIN  Peaks p ON P.MountainId= M.Id
LEFT JOIN CountriesRivers cr ON cr.CountryCode= c.CountryCode
LEFT JOIN Rivers r ON R.Id= CR.RiverId
  ) AS P
  WHERE P.Rank=1 AND P.Rankr=1
  ORDER BY HighestPeakElevation DESC,
  LongestRiverLength DESC,P.CountryName ASC