Find max value based on ending values-CodePudding

I have a table that is similar to the one below:

Name	Current submission	Submission	Date
A	43-01	43-01	1-1-2019
A	43-01	42-01	1-1-2020
A	43-01	42-02	1-1-2021
A	43-01	42-03	1-1-2022

I want it to look like the following:

Name	Current submission	Submission	Date
A	42-03	43-01	1-1-2019
A	42-03	42-01	1-1-2020
A	42-03	42-02	1-1-2021
A	42-03	42-03	1-1-2022

Here is the code I used to get the first table:

SELECT 
   Submission, 
   MAX(Submission) over (partition by Name) as [Current Submission], 
   Date
FROM Table_Name

I have tried to do a CASE WHEN statement using MAX(Date) but I can't get the logic right...

CodePudding user response：

I was literally writing this when the last comment was made.

select name, 
first_value(submission) over (partition by name order by date desc) as current_submission, 
submission, 
date
from my_table

CodePudding user response：

Thank you to shawnt00 with the following solution

SELECT
   Submission,
   FIRST_VALUE(Submission) OVER (PARTITION BY Name ORDER BY DATE DESC),
   DATE,
FROM Table_Name