How to retrieve first and last row in an aggregate
Question

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.


Thanks,

Unnati K.

Hi Unnati, it makes sense now, thank you very much. 


Regards,

Ruru

mvp_badge
MVP

Hi Rudo,

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.


Regards,

-PJ-

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.


Thanks,

Unnati K.

Hi Unnati, it makes sense now, thank you very much. 


Regards,

Ruru

Thank you

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.