Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT c.CountryName AS Country,
- CASE
- WHEN MAX(ISNULL(p.Elevation, 0)) = 0 THEN '(no highest peak)'
- ELSE (SELECT PeakName FROM Peaks WHERE Elevation = MAX(ISNULL(p.Elevation, 0)))
- END AS [Highest Peak Name],
- MAX(ISNULL(p.Elevation, 0)) AS [Highest Peak Elevation],
- CASE
- WHEN MAX(ISNULL(p.Elevation, 0)) = 0 THEN '(no mountain)'
- ELSE (SELECT Mountains.MountainRange FROM Mountains LEFT JOIN Peaks ON Peaks.MountainId = Mountains.Id WHERE Peaks.Elevation = MAX(ISNULL(p.Elevation, 0)))
- END AS Mountain
- FROM Countries AS c
- LEFT JOIN MountainsCountries AS mc ON mc.CountryCode = c.CountryCode
- LEFT JOIN Mountains AS m ON m.Id = mc.MountainId
- LEFT JOIN Peaks AS p ON p.MountainId = m.Id
- GROUP BY c.CountryName
- ORDER BY c.CountryName, [Highest Peak Elevation]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement