How can I create new column in sql based on previous date?-CodePudding

Col A         
1 Jan
2 Jan
3 Jan
5 Jan
6 Jan

Output

Col A      Col B
1 Jan    NULL
2 Jan    1 Jan
3 Jan    2 Jan
5 Jan    3 Jan
6 Jan    5 Jan

CodePudding user response：

You can try with LAG() as suggested by @Akina

WITH D AS (
SELECT A FROM YOUR_TABLE 
) 
SELECT A, LAG(A, 1, 0) OVER(ORDER BY A DESC)
FROM D

EDIT i see you are in mysql just now, you can try with this answer as well

CodePudding user response：

Use the window function LAG().

SELECT *,
  LAG(col_a) OVER (ORDER BY col_a)
FROM t;