Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- with
- PeaksInfo_CTE(Country, Mountain, Peak, Elevation)
- as
- (select
- [C].CountryName,
- [M].MountainRange,
- [P].PeakName,
- [P].Elevation
- from
- Countries as [C]
- left join
- MountainsCountries as [MC]
- on
- [C].CountryCode = [MC].CountryCode
- left join
- Mountains as [M]
- on
- [MC].MountainId = [M].Id
- left join
- Peaks as [P]
- on
- [M].Id = [P].MountainId),
- HighestPeakInfo_CTE(Country, Elevation)
- as
- (select
- Country,
- max(Elevation)
- from
- PeaksInfo_CTE
- group by
- Country)
- select top 5
- [A].Country,
- case
- when [A].Peak is null
- then '(no highest peak)'
- else [A].Peak
- end as [Highest peak name],
- case
- when [A].Elevation is null
- then 0
- else [A].Elevation
- end as [Elevation],
- case
- when [A].Mountain is null
- then '(no mountain)'
- else [A].Mountain
- end as [Mountain name]
- from
- PeaksInfo_CTE as [A]
- left join
- HighestPeakInfo_CTE as [B]
- on
- [A].Country = [B].Country
- where
- [A].Mountain is null
- and [A].Peak is null
- and [A].Elevation is null
- or [A].Elevation = [B].Elevation
- order by
- [A].Country asc,
- [A].Peak asc
Advertisement
Add Comment
Please, Sign In to add comment