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

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

CodePudding user response：

You can do this using the analytic function MAX() Over()

SELECT date, MAX(date) OVER() - date FROM mytable;

Tried this here on sqlfiddle

CodePudding user response：

If you want the date difference, you can use:

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