Select a certain value when there are duplicates, while untying on highest date

I have two tables A and B. The common key between the two tables is "key". I am joining the two tables to get the following table: ID, Date, Value. This is the code I use:

SELECT DISTINCT B.ID, A.Date, A.Value
FROM       A 
INNER JOIN B ON A.key = B.key

For a certain ID it is ok to have multiple dates but these dates should be different. When the same date appears more than once, a different column called "Date2" should be checked in table A and select only the value with the most recent "Date2"

Example from Table A:

In the final table only the second line should be present because the Date2 value is more recent.

I am not sure how to go about with this. Any help is much appreciated.

>Solution :

If your DBMS supports window functions, you should use FIRST_VALUE on both "B.ID" and "A.Value", while:

• partitioning on "Date"
• ordering on "Date2" descendently

This will ensure that each "A.Date" will have only one record, whose "B.ID" and "Value" will be the highest possible.

SELECT DISTINCT 
       FIRST_VALUE(B.ID) OVER(PARTITION BY A.Date ORDER BY A.Date2 DESC) AS ID,
       A.Date,
       FIRST_VALUE(A.Value) OVER(PARTITION BY A.Date ORDER BY A.Date2 DESC) AS Value
FROM       A 
INNER JOIN B ON A.key = B.key 

If your ID is the same across the same "A.Date", you can replace the first window function with only "B.ID".