18
Views
3
Comments
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.

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

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-

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

Thank you