mysql querying to combine two columns in a table

Question

I want to query like this :

Suppose if you have a table like where X and Y are columns.

X Y

A 1
B 2
C 3
D 4

and now you want to create something like:

X Y Z
A 1 2
B 2 3
C 3 4
D 4 5

Basically you want to create a pair with the next row value here. The pairs are (Y, Z) like (1, 2), (2, 3), (3, 4). How will I write mysql query for something like this ? Any pointers?

Answer 1

From what I see, the value of Z is 1+value of Y.

Have a look at this simple query :-

SELECT x,y,(y+1) AS Z from test

SQL Fiddle :-

http://sqlfiddle.com/#!9/08b1e/2