CODEWITHSUNDEEP
sqloracle-database
Schwartz914
Schwartz914

Reputation: 35

Oracle SQL. Selecting the MAX(Date)

Need to update values in a table. Essentially I have added a fromDate and toDate columns. The values I am setting at thepriceDate with:

UPDATE schema.myTable SET FROMDATE = PRICEDATE, TODATE = PRICEDATE WHERE FROMDATE IS NULL AND TODATE IS NULL

This works. I then need to set the latest date to 6000-01-01. All the rows are unique but can relate to similar objects. So I may have:

priceDate | fromDate | toDate | INSTRUMENT | uniqueID 25/09/2018 25/09/2018 25/09/2018 AUD | 1 26/09/2018 26/09/2018 26/09/2018 EUR | 2 25/09/2018 25/09/2018 25/09/2018 EUR | 3 25/09/2018 25/09/2018 25/09/2018 JPN | 4 29/09/2018 29/09/2018 29/09/2018 EUR | 5 29/09/2018 29/09/2018 29/09/2018 JPN | 6 20/09/2018 20/09/2018 20/09/2018 JPN | 7 28/09/2018 28/09/2018 28/09/2018 AUD | 8

I have tried: UPDATE schema.myTable SET TODATE = TO_DATE('60000101', 'yyyymmdd') WHERE TODATE in ( SELECT MAX(TODATE) FROM schema.myTable WHERE HISTTYPE = 'Close' GROUP BY INSTRUMENT );

But that gives me multiple of the same Instrument where I only want one of each. EDIT:

So after my Query I want:

priceDate | fromDate | toDate | INSTRUMENT | uniqueID 25/09/2018 25/09/2018 25/09/2018 AUD | 1 26/09/2018 26/09/2018 26/09/2018 EUR | 2 25/09/2018 25/09/2018 25/09/2018 EUR | 3 25/09/2018 25/09/2018 25/09/2018 JPN | 4 29/09/2018 29/09/2018 01/01/6000 EUR | 5 29/09/2018 29/09/2018 01/01/6000 JPN | 6 20/09/2018 20/09/2018 20/09/2018 JPN | 7 28/09/2018 28/09/2018 01/01/6000 AUD | 8

Upvotes: 1

Views: 6416

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I think you just need a correlated subquery:

UPDATE schema.myTable t
    SET TODATE = TO_DATE('60000101', 'yyyymmdd') 
    WHERE TODATE = ( SELECT MAX(tt.TODATE)
                     FROM schema.myTable tt
                     WHERE tt.HISTTYPE = 'Close' AND
                           tt.INSTRUMENT = t.INSTRUMENT
                   );

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

If understand correctly then you can use row_number window function and find max date of per group of INSTRUMENT then update those

SET TODATE = TO_DATE('60000101', 'yyyymmdd') 
WHERE TODATE in ( 
select TODATE from
(
SELECT TODATE ,row_number() over(partition by INSTRUMENT order by TODATE desc) as rn FROM schema.myTable WHERE HISTTYPE = 'Close'
) as t where t.rn=1
 );

Upvotes: 0

Related Questions