Filter by Last Date (GroupBy)

Filter by Last Date (GroupBy)

  

Hi All,

I want to show on a list the last payment made per 'project' (regardless of the dates) this is just an example:


Any ideas how to filter by the last date? I only want to show 1 row by project. 


Solution

André P wrote:

Hi All,

I want to show on a list the last payment made per 'project' (regardless of the dates) this is just an example:


Any ideas how to filter by the last date? I only want to show 1 row by project. 

Hi André,

In the column DatePaid context menu (click in the symbol that will appear when you hover it, select the Max option. This will add the last date to the grouped attributes.

https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/Aggregate_a_Column_into_a_Single_Value

And that's it :)

Cheers.

Solution

Eduardo Jauch wrote:

André P wrote:

Hi All,

I want to show on a list the last payment made per 'project' (regardless of the dates) this is just an example:


Any ideas how to filter by the last date? I only want to show 1 row by project. 

Hi André,

In the column DatePaid context menu (click in the symbol that will appear when you hover it, select the Max option. This will add the last date to the grouped attributes.

https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/Aggregate_a_Column_into_a_Single_Value

And that's it :)

Cheers.

Thank you Eduardo, that does do the trick but then I am struggling to then get the corresponding value column to match... 

Hi André,

You need to use an advance query for that. Check how in this link https://stackoverflow.com/questions/19432913/select-info-from-table-where-row-has-max-date.

Regards,

Marcelo

Marcelo that certainly looks like the right answer but I will have to figure out how to use advance query (first time using one and using SQL syntax). Tried this (below) but gave me invalid object name 'Project'.

SyntaxEditor Code Snippet

SELECT Label, DatePaid, Value
FROM Project t
inner join 
(SELECT Label,MAX(DatePaid) as max_date
FROM Project
GROUP BY Label)a
on a.Label = t.Label and a.max_date = DatePaid


Hi Andre,

Sorry, I didn't understand that you needed the other fields. I think may be possible with aggregate, but probably would not have good performance.

SQL in OutSystems needs the name of the entities between { } and the fields between [ ].

An entity alias do not need to be between { }.

Cheers