Log in to AnswerLog in to Follow
2019-04-10 02-57-14
RUDO MURIEL MUNYAVI
206Views
6Comments
Solved
How to retrieve first and last row in an aggregate
Question
Logic

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.

0
0
26 Dec 2020

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

See solution in context
1
0
26 Dec 2020

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

0
0
24 Nov 2021

Replying to Ma Jatrina Luella Fontilla's comment on 24 Nov 2021 08:25:13

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.

2
0
24 Nov 2021

Replying to Unnati Khanorkar's comment on 24 Nov 2021 09:29:12

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


Regards,

Ruru

0
0
26 Nov 2021
2018-10-07 01-52-13
Pramod Jain
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-

0
0
26 Dec 2020

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

1
0
26 Dec 2020

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

0
0
24 Nov 2021

Replying to Ma Jatrina Luella Fontilla's comment on 24 Nov 2021 08:25:13

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.

2
0
24 Nov 2021

Replying to Unnati Khanorkar's comment on 24 Nov 2021 09:29:12

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


Regards,

Ruru

0
0
26 Nov 2021

Thank you

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