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

Question

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

I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — , Sep 28 '21 at 07:58

Carlo Prato · Accepted Answer · 2021-10-12T07:56:48.027

0

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

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

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

edited Oct 12 '21 at 07:56

answered Sep 28 '21 at 07:55

Carlo Prato

1

Need to remove 0 from LAG, i.e., LAG(A,1). There are some other columns as well, so I used partition in OVER () and it worked. Thanks !! – ajain Oct 04 '21 at 13:20
yeew good! maybe accept the answer then :) – Carlo Prato Oct 06 '21 at 07:19

score 0 · Answer 2 · answered Sep 28 '21 at 07:56

0

Use the window function LAG().

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

answered Sep 28 '21 at 07:56

Jim Jones

score 0 · Answer 3 · answered Oct 04 '21 at 13:25

0

WITH D AS ( SELECT date FROM table ) SELECT date, LAG(date, 1) OVER(ORDER BY date) FROM D

answered Oct 04 '21 at 13:25

ajain

3 Answers3