CODEWITHSUNDEEP
sqloracle-database
Miguel
Miguel

Reputation: 2079

Select max column value when column value does not exist?

I have a table with the following data below. I'm struggling with the sql to select the row with the max fiscal_period and fiscal_year if the where clause in my sql does not exist in the table.

so in other words , if i try to select fiscal year 8 which does not yet exist , my query should retrieve the row with the max period and year that does exist. can anyone help me with a query that would provide that answer?

here is what i tried so far.

    SELECT   
 nvl(FISCAL_PERIOD,max(FISCAL_PERIOD)),nvl(FISCAL_year,max(FISCAL_year)),amount  
        FROM MAXTABLE   
        where fiscal_period = 5  
        group by FISCAL_PERIOD,FISCAL_year, amount;


CREATE TABLE MAXTABLE  
   (    "FISCAL_PERIOD" NUMBER,  
         "FISCAL_YEAR" NUMBER,  
         "AMOUNT" NUMBER  
   ) 


 Insert into MAXTABLE (FISCAL_PERIOD,FISCAL_YEAR,AMOUNT) values (1,9,50);  
    Insert into MAXTABLE (FISCAL_PERIOD,FISCAL_YEAR,AMOUNT) values (2,5,50);  
    Insert into MAXTABLE (FISCAL_PERIOD,FISCAL_YEAR,AMOUNT) values (3,6,50);

Upvotes: 3

Views: 1483

Answers (1)

Bohemian
Bohemian

Reputation: 424993

Use a union all with the query to get data for the max year with an additional not exists condition for the target year, so both halves of the union can't be returned:

SELECT FISCAL_PERIOD, FISCAL_year, amount  
FROM MAXTABLE   
where fiscal_period = 5  
union all
SELECT FISCAL_PERIOD, FISCAL_year, amount  
FROM MAXTABLE 
where fiscal_period = (select max(fiscal_period) from MAXTABLE)
and not exists (select * from MAXTABLE where fiscal_period = 5)

Upvotes: 3

Related Questions