Trying to pull most recent record by date

[#1]

I have a recordset which looks like this.

Request ID          Record Created Date          Name         

1                         2/5/2012                        AAA

1                         5/15/2013                        AAA

1                         4/3/2014                         AAA

2                         3/3/2014                         BBB

3                         6/25/2013                        CCC

3                         8/2/2014                         CCC

I need the resulting table to look like this

Request ID          Record Created Date     Name

1                         4/3/2014                         AAA

2                         3/3/2014                         BBB

3                         8/2/2014                         CCC

I have tried numerous things to no avail.  I would like to use in both a straight table and in the load script.

Any suggestions?

[Gysbert_Wassenaar · Accepted solution]

Create a straight table with Request ID as dimension and two expressions:

• max([Record Created Date])
• firstsortedvalue(Name, -[Record Created Date])

If you want to do this in the script you can use the same functions:

Summary:

LOAD

     [Request ID],

     max([Record Created Date]) as MaxRCD,

     firstsortedvalue(Name, -[Record Created Date]) as NameMaxRCD

FROM ...

GROUP BY [Request ID];

[#3]

Thank you.  I took this as the base and have been using this same thing with other fields in my table that are causing duplicate rows and it is working to pull only the most recent record created. 

[kfloresc]

I'm getting an effort right before the FROM.

SQL SELECT ....

...

...

FROM ...

how do I edit the Select portion?