Select the maximum column value if the column value doesn't exist?
I have a table with the following data below. I am struggling with sql to select a row with max financial_period and financial_year if the where clause in my sql doesn't exist in the table.
so, in other words, if I try to select fiscal year 8 that doesn't exist yet, my query should get a row with the maximum period and year that exists. can anyone help me with a query that will provide this answer?
here is what i have 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);
+3
source to share
1 answer
Use union all
with a query to get data for a maximum year with an additional condition not exists
for the target year, so both halves of the join cannot 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)
+3
source to share