Implement a "top" or "maxrows" in Ad-Hoc query
Please implement a way to limit the number of results returned by a query, similar to the "top" directive in SQL. This would simplify queries where you are not interested in a complete dataset, such as "top 100 donors to a fund" or "10 most recent donors." It would also improve performance by returning only the needed records.
-
Matt Turkington
- Mar 17 2021
- Reviewed: Voting Open
| Organization Name (Please enter full organization name) | Providence St. Joseph Health |
Related ideas
I echo @David Wanless' comments.
Thanks, Nicola. If anyone has done this via customisation (as a general solution for all Ad-hoc Queries, not just for one query), I'd be interested to hear how they did it. Has Blackbaud done this ?
After further review, I have updated the title of this ides to relate to Ad-hoc query behaviour and set the Status to Reviewed: Voting Open. David Wanless - I believe currently this would be achieved via a customization.
Dear Nicola,
How did this solution work for non 'Donor list' Smart Queries? E.g., if we want to define a Query which returns the 100 Constituent records with the highest value of a numeric Attribute, the Parameters tab doesn't appear to have a 'top' selection, so this approach seems not to be applicable.
What is required is a general solution which allows any Ad-hoc Query to be limited to returning n records.
Kind regards,
David.
The flexibility of allowing top n in any ad hoc query would be huge. Smart queries can be built to manage certain situations, but relying on smart queries to be the sole means of doing this requires a new smart query spec for any new record type or change in parameter options/grouping/outputs that may come up.
Yes, but I don't think that works with the ad-hoc query REST API endpoint.
This can be achieved via the use of Smart Queries. For example the Donor List smart query allows you to select specific criteria and return only the required number of results you need: