Pull Top Three Entries in a Data Set
Answered
Is there a way I can pull the last entry and/or last three entries of a data set using a calculated field?
Is there a way I can pull the last entry and/or last three entries of a data set using a calculated field?
The same question
Hi An,
Thanks for reaching out. You can get the last result of a data set by using the MAX function, like so:
You can also use MIN for the first entry, which in my case is a NULL value:
This is all in isolation though. Depending on what else you pull in it may be difficult to get the results you'd be looking for.
In standard SQL you can use things like LIMIT in MySQL, TOP in SQL Server, and ROWNUM in Oracle, but these are not supported using Freehand SQL:
There are of course ways to manually obtain this information - for example, sorting the field in question in descending order, then bringing in a filter for said field and manually choosing those 3 values in a list. This is of course not dynamic so it would only be good for static reports. If you want to display 3 most recent data entries, but not just the 3 most recent data entries you can just leave the field in the report sorted as descending, so each time it's reloaded the last entry is on top of the report.
Ultimately, I'm afraid data-specific SQL solutions are a bit outside the scope of Support, so I'm afraid there's not much else I can add here, but please let me know if you have any further questions on anything I've mentioned thus far.
Regards,
Mike
Hi An,
Thanks for reaching out. You can get the last result of a data set by using the MAX function, like so:
You can also use MIN for the first entry, which in my case is a NULL value:
This is all in isolation though. Depending on what else you pull in it may be difficult to get the results you'd be looking for.
In standard SQL you can use things like LIMIT in MySQL, TOP in SQL Server, and ROWNUM in Oracle, but these are not supported using Freehand SQL:
There are of course ways to manually obtain this information - for example, sorting the field in question in descending order, then bringing in a filter for said field and manually choosing those 3 values in a list. This is of course not dynamic so it would only be good for static reports. If you want to display 3 most recent data entries, but not just the 3 most recent data entries you can just leave the field in the report sorted as descending, so each time it's reloaded the last entry is on top of the report.
Ultimately, I'm afraid data-specific SQL solutions are a bit outside the scope of Support, so I'm afraid there's not much else I can add here, but please let me know if you have any further questions on anything I've mentioned thus far.
Regards,
Mike
Hi An,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi An,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Hi An,
I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Hi An,
I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.
Regards,
Mike
Replies have been locked on this page!