MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table accounts (name varchar(8), c1 int, c2 int)
insert into accounts values ('test1', 5, 0)
insert into accounts values ('test2', 1, 6)
insert into accounts values ('test3', 5, 11)
insert into accounts values ('test4', 0, 0)
with ranked as (
    select name, (c1 + c2) as total, 
    dense_rank () over (order by (c1+c2) desc) rnk
    from accounts a 
) select r.name, r.total, r.rnk, p.name "lower ranked", n.name "higher ranked"  
from ranked r
left join ranked p on p.rnk = r.rnk+1
left join ranked n on n.rnk = r.rnk-1
order by r.rnk


name
total
rnk
lower ranked
higher ranked


test3
16
1
test2
null

test2
7
2
test1
test3

test1
5
3
test4
test2

test4
0
4
null
test1

name	total	rnk	lower ranked	higher ranked
test3	16	1	test2	null
test2	7	2	test1	test3
test1	5	3	test4	test2
test4	0	4	null	test1