How to retrieve first and last row in an aggregate

Rank: #3028

Solved

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.

on 2020-12-26

Unnati Khanorkar

Rank: #399

Solution

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

on 2020-12-26

Pramod Jain

mvp_badge

MVP

Rank: #51

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-

on 2020-12-26

Unnati Khanorkar

Rank: #399

Solution

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

on 2020-12-26

RUDO MURIEL MUNYAVI

Rank: #3028

Thank you

on 2020-12-26

Training

Documentation

How to retrieve first and last row in an aggregate