Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Create a straight table with Request ID as dimension and two expressions:
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];
Create a straight table with Request ID as dimension and two expressions:
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];
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.
I'm getting an effort right before the FROM.
SQL SELECT ....
...
...
FROM ...
how do I edit the Select portion?