Hi. How do i retrieve first and last row in an aggregate. Attached is a pic of my aggregate. i want to retrieve the opening balance which is the first record and also the closing balance which is the last row.
How can i get this without duplicating the aggregate and sorting by descending to get the last balance.
Hi Rudo,
If you are just using the aggregate then you can access the first and last element as follows -
The first element is as list[0], and the last will be list[lengthOfAggregateResult-1].
Before doing this please check for the length of the aggregate result if it is greater than 0.
Thanks,
Unnati
Hi Unnati,
Can you explain to me what does this means list[length Of Aggregate Result-1] ? Why do we need to add -1 ?
Sorry, Im still newbie to Outsystems.
Thank you,
Ruru
Hi Ruru,
We are subtracting 1 from the length to get the last element because the indexing of the elements in the list starts from 0. e.g.
1. let say, Length of the aggregate list is 4.
2. So the indexing will be
a. First element -> 0
b. Second element -> 1
b. Third element -> 2
b. Fourth element -> 3
3. So when we are accessing the elements with the index values we have to subtract 1 from the length of the any array or list.
Let me know if this helps.
Unnati K.
Hi Unnati, it makes sense now, thank you very much.
Regards,
You can use a advance query to get only first and last record ..see below .
This is just an example you need to tweak as per your requirement.
(SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC LIMIT 1) UNION ALL (SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date ASC LIMIT 1)
Please try something like this and let me know if it helps.
-PJ-
Thank you