TonyTroev

Highest peak name and elevation by country

Feb 2nd, 2019
171
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.12 KB | None | 0 0
  1. with
  2.     PeaksInfo_CTE(Country, Mountain, Peak, Elevation)
  3.     as
  4.         (select
  5.             [C].CountryName,
  6.             [M].MountainRange,
  7.             [P].PeakName,
  8.             [P].Elevation
  9.         from
  10.             Countries as [C]
  11.         left join
  12.             MountainsCountries as [MC]
  13.         on
  14.             [C].CountryCode = [MC].CountryCode
  15.         left join
  16.             Mountains as [M]
  17.         on
  18.             [MC].MountainId = [M].Id
  19.         left join
  20.             Peaks as [P]
  21.         on
  22.             [M].Id = [P].MountainId),
  23.     HighestPeakInfo_CTE(Country, Elevation)
  24.     as
  25.         (select
  26.             Country,
  27.             max(Elevation)
  28.         from
  29.             PeaksInfo_CTE
  30.         group by
  31.             Country)
  32. select top 5
  33.     [A].Country,
  34.     case
  35.         when [A].Peak is null
  36.             then '(no highest peak)'
  37.         else [A].Peak
  38.     end as [Highest peak name],
  39.     case
  40.         when [A].Elevation is null
  41.             then 0
  42.         else [A].Elevation
  43.     end as [Elevation],
  44.     case
  45.         when [A].Mountain is null
  46.             then '(no mountain)'
  47.         else [A].Mountain
  48.     end as [Mountain name]
  49. from
  50.     PeaksInfo_CTE as [A]
  51. left join
  52.     HighestPeakInfo_CTE as [B]
  53. on
  54.     [A].Country = [B].Country
  55. where
  56.     [A].Mountain is null
  57.     and [A].Peak is null
  58.     and [A].Elevation is null
  59.     or [A].Elevation = [B].Elevation
  60. order by
  61.     [A].Country asc,
  62.     [A].Peak asc
Advertisement
Add Comment
Please, Sign In to add comment