Search code examples
sqlapache-sparkapache-spark-sqldatediff

How do I create a new column showing difference between maximum date in table and date in row?

I need two columns: 1 showing 'date' and the other showing 'maximum date in table - date in row'. I kept getting a zero in the 'datediff' column, and thought a nested select would work.

SELECT date, DATEDIFF(max_date, date) AS datediff
     (SELECT MAX(date) AS max_date
       FROM mytable)
FROM mytable
GROUP BY date

Currently getting this error from the above code : mismatched input '(' expecting {, ';'}(line 2, pos 2) Correct format in the end would be:

    date    |   datediff
--------------------------
2021-08-28  |     0
2021-07-26  |     28
2021-07-23  |     31
2021-08-11  |     17
Solution

  • If you want the date difference, you can use:

    SELECT date, DATEDIFF(MAX(date) OVER (), date) AS datediff
FROM mytable
GROUP BY date