Oracle: How to get first returned record and previous record-CodePudding

I want to grab the first returned record and previous record based on a user input date.

CustNo  Food                Date
1       Red-Apple           7/5/22
1       Red-Apple           7/5/22
1       Red-Apple           7/11/22
1       Red-Cherry          5/20/22
1       Blue-Muffin         4/1/22 
1       Blue-Berry          3/16/22
1       Orange-Persimmon    2/8/22
1       Red-Apple           1/23/22
1       Blue-Berry          12/4/21
1       Yellow-Banana       11/27/21

Example, I put in 7/5/22, and I want to grab that date's food value and somehow include the record previous to that, so output would be:

1       Red-Apple           7/5/22
1       Red-Apple           7/5/22

If I put in 3/16/22, then I want my output to be:

1       Blue-Berry          3/16/22
1       Orange-Persimmon    2/8/22

My totally wrong code:

select CustNo, Food, prevDate
from (
    select CustNo, Food, Date
        lag(Date) over (partition by CustNo order by Date) as prevDate,
        max(Date) over (partition by CustNo) as maxDate
    from tableZ
)
where maxDate = Date
and CustNo = 1
and Date = &user_input_date;

CodePudding user response：

Simply select the rows where the date is equal to or less than the decided date, read descending, fetch 2 rows only.

select CustNo, Food, Date
from tableZ
where Date <= &user_input_date
order by Date desc
fetch first 2 rows only

Note: Date is an Oracle reserved word (https://en.wikipedia.org/wiki/SQL_reserved_words), so it needs to be delimited as "Date".