calculate upcoming category based on the dates of the past categories [closed]

I have the above table structure in the database and I want to calculate the upcoming abbreviation for an id (if a particular abbreviation has an actual date present (achieved) then it should give the current_date(which will be upcoming date even if it’s in past) and next upcoming abbreviation for that particular id): if the last abbreviation has an actual date (for which category is also present).

expected result:

I’m new to SQL, i guess it can be achieved by taking maximum of actual_date present for abbreviation for a id.

Answer

If you want the abbreviation for each id with the maximum actual date, you can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by actual_date desc) as seqnum
      from t
      where actual_date is not null
     ) t
where seqnum = 1;