Join Tables with Max Date

[alec1982]

Hi Guys,

I have two tables:

Tables1:

Reference,

Name,

Option

Table2:

Reference,

Date,

Note,

The relationship is one to many like each reference might have several notes..

I am trying to join table two to table one to have the latest Note based on the date.

Please let me know your thoughts..

Best,

Alec

[Oleg_Troyansky]

Look at function FirstSortedValue, I believe that's what you need to get the latest note.

[alec1982 · Accepted solution]

I have figured a way to get this done.

I have created a temp table to store Reference and max Date.

Then I created a new table to left join the previous table with the rest of the data.

Thxs for all your help