Select value and next highest value from rows in same table

Question

I have a table like this

LowerPoints MessageTemplate
0           zero
10          ten
100         hundred

and I would like to select from it so I have a list of lowerPoints and then the NEXT lowest value of LowerPoints

e.g.

select
    low.LowerPoints As LowerPoints
    , high.LowerPoints as UpperPoints
    , low.MessageTemplate as MessageTemplate
from MessageTemplate low
    , MessageTemplate high
where high.LowerPoints = (select top 1 LowerPoints from MessageTemplate where LowerPoints > low.LowerPoints order by LowerPoints)

which returns

LowerPoints UpperPoints MessageTemplate
0           10          zero
10          100         ten

but I cannot see how to get the third value and give it a "null" value for UpperPoints. i.e. the last row would be

100                     hundred

Answer 1

Your usage of the outdated syntax, separating tables by comma would be read as INNER JOIN. You could write it as:

select
    low.LowerPoints As LowerPoints
    , high.LowerPoints as UpperPoints
    , low.MessageTemplate as MessageTemplate
from MessageTemplate low
LEFT JOIN MessageTemplate high
ON high.LowerPoints = (select top 1 LowerPoints from MessageTemplate where LowerPoints > low.LowerPoints order by LowerPoints)

You might also use a simple subselect

Declare @a Table (LowerPoints int, MessageTemplate varchar(30))
insert into @a Values(0,'zero'),(10,'ten'),(100,'hundred')

Select a.LowerPoints
     ,(Select top 1 LowerPoints from @a b 
       where b.LowerPoints>a.LowerPoints
       order by b.LowerPoints) as UpperPoints
     ,a.MessageTemplate
from @a a
Order by LowerPoints

or a CTE generating a defined row number (rn) with a LEFT JOIN to itself for the condition b.rn = a.rn + 1

;With CTE as
(
Select LowerPoints,MessageTemplate
       ,ROW_NUMBER() OVER (ORDER by LowerPoints) as rn
from @a a
)
Select c1.LowerPoints
      ,c2.LowerPoints as UpperPoints
      ,c1.MessageTemplate 
from CTE c1
LEFT JOIN CTE c2 on c2.rn=c1.rn +1 
Order by c1.LowerPoints