All geek questions in one place

SQL - get the maximum value of an existing field as a separate field in the same dataset

I have a SQL problem. I am running a script in Oracle 11 using 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 maximum date value as a separate 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 general script, I cannot do this using a subquery. A simple subquery 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 value "Table1" is a very long script with some specific parameters. If I were to copy this script into a previous request, I would need to double when defining my parameters at runtime.

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

thanks

E

+5
source share
1 answer

Use the window function:

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

Source: https://habr.com/ru/post/1244589/

More articles:


All Articles