Shifting the total group by 1 step- MYSQL

Question

Here I have name and pay as columns of a database table.

Table has Name and Pay(sorted lowest to highest) as columns and "column I need" is the required column.

I need the rightmost column, it is displaying basically the next pay. Gross, Stanford pay is 14, the next highest pay is 15. Likewise for others.

How can I do this? Any help is appreciated.

Answer 1

I understand that you want the "next" value pay.

If you are running MySQL 8.0, you can use window functions and a range frame:

select t.*,
    min(pay) over(order by pay range between 1 following and unbounded following) next_pay
from mytable t

In earlier versions, an alternative is a correlated subquery:

select t.*,
    (select min(t1.pay) from mytable t1 where t1.pay > t.pay) next_pay
from mytable t