SQL - Get max value of existing field as stand alone field in same data set

Question

I am having a SQL issue. I am running a script on Oracle 11 using the Squirrel SQL Client. A simple example of my problem. I have the following data:

ID  Date
1   2016-01-01
2   2016-01-02
3   2016-01-03
4   2016-01-04
5   2016-01-05
6   2016-01-06
7   2016-01-07
8   2016-01-08
9   2016-01-09
10  2016-01-10

I would like to create a new field that returns the max date value as a stand alone field:

ID  Date        Max_Date
1   2016-01-01  2016-01-10
2   2016-01-02  2016-01-10
3   2016-01-03  2016-01-10
4   2016-01-04  2016-01-10
5   2016-01-05  2016-01-10
6   2016-01-06  2016-01-10
7   2016-01-07  2016-01-10
8   2016-01-08  2016-01-10
9   2016-01-09  2016-01-10
10  2016-01-10  2016-01-10

Due to the complexity of my overall script, I cannot do this using a sub-query. A simple sub-query solution might look like this.

SELECT a.ID, 
       a.DATE,
       b.MAX_DATE
  FROM TABLE1,(SELECT ID, 
                      max(DATE) MAX_DATE
                 FROM TABLE1
               ) b
 WHERE 1=1

However my "Table1" value is a very long script with some defined parameters. If I were to copy this script into the sub query above, I would need to double up when defining my parameters at run time.

So I am wondering, is it possible to take an existing field within a table and create an additional field that repeats the max value of the existing field for all rows?

Thanks

E

Answer 1

Use a window function:

SELECT a.ID, 
       a.DATE,
       max(a.date) over () as max_date
FROM table1 a