Sort: unique keys and ties
Cut: fixed N or keep ties
Other: conserves the authorized total
Access: filter before ranking
Narrative: do not invent the tail
The top N is not the total; the tail needs its own bucket
Adding up the top ten customers is not supposed to equal sales for every customer. When an AI query treats the leaderboard as the whole answer, the truncated tail never appears in the table and never enters the total, so finance is left with a gap nobody can name. Top-N needs its own contract: sort keys, a tie policy, an Other bucket, an authorization scope, and a narrative boundary, all fixed in the query rather than filled in by the model afterward.
Example: a region has two hundred customers. The top ten sum to one amount and the rest sum to another; only both pieces equal sales inside the authorized population. A page that prints ten rows and omits Other invites people to treat the top ten as the total, or to guess that the gap is refunds, tax, or a definition change. Show the ten rows plus one Other row that returns to the same total. Other carries the sum and the customer count, not invented names for customers the query did not return.
LIMIT and TOPN do not truncate the same way
PostgreSQL documents that LIMIT returns at most the requested number of rows, and that ORDER BY must constrain a unique order or the subset is unpredictable. The planner also considers LIMIT, so a different limit can change the plan and the row order. Sorting by sales descending and taking ten rows is unstable when the tenth and eleventh amounts match, or when no stable customer key breaks the tie: a refresh can swap members and change the displayed sum.
DAX TOPN behaves differently. If values tie at the Nth row, every tied row is returned, so the result can contain more than N rows. TOPN also does not guarantee any sort order in the output. An enterprise query should not assume that “top ten” means one engine’s behavior. The semantic layer should state whether the contract is a strict N with ties broken, or a tie-inclusive cut that may exceed N, and that statement should be visible on the answer.
Ties and unstable ordering rewrite tenth place
Ties are ordinary. After a shared contract price, a promotion, or rounding, several customers can show the same sales amount. A strict cut needs second and third sort keys, such as a customer identifier, with a declared direction. Those keys only break ties; they do not redefine what rank means. If the business wants tied customers to enter or leave together, keep the whole tie group and accept more than N rows.
Instability also comes from undeclared nulls, mixed currencies, and the wrong grain. Kimball’s grain rule asks what one fact row represents: an order, an order line, or an already aggregated customer-day. Truncating line items can turn a “top ten” into ten product rows. A star schema keeps measures on the fact table and customers on a dimension; ranking should happen on those dimension members, not on whatever physical row the engine happens to emit.
The Other bucket has to conserve the total
Other means the population total minus the displayed set, under the same metric, grain, filters, and watermark. It is not the set of customers a model considers unimportant, and it is not the leftover after two queries round on their own. Displayed rows, Other, and the total should conserve. If refunds, currency conversion, or rounding apply, state the rule in advance and show the rounding residual on its own line instead of hiding it inside Other.
By default Other shows a sum, a member count, and whether blank dimension members are included. Blank customers, test accounts, and unmatched keys need an explicit policy: fold them into Other, list them apart, or exclude them. Truncation must not randomly push nulls into or out of the top N. If someone asks who is largest inside Other, rank again inside that set. Do not treat the first truncated tail as a roster the system already knows.
Filter on permission before the cut, or the question changes
The order of security filters and truncation changes the question. When a regional manager asks for their own top ten customers, apply the visible scope first, rank inside that population, and set Other to the authorized total minus the rows shown. Taking a company-wide top ten and then filtering can leave fewer than ten visible rows. If Other still uses the company total, amounts for customers the manager cannot see fall into the residual. That residual will not match the manager’s own total, and its size can hint at volume outside their scope.
A headquarters question about the company-wide top ten is a different question: the population is whoever the asker is allowed to see, and the cut happens there. A shared service account cannot stand in for that person. Each execution should bind the current identity, and cache keys should include both the identity scope and the truncation parameters. Truncating first and filtering afterward fits only a reviewed pattern that shows a global board and then masks unauthorized detail. It is the wrong default for a regional question.
The model must not rebuild the tail from memory
When the tool returns ten rows, the model context does not contain customer eleven and beyond. A sentence that names dealers who never appeared, or that describes the remainder from memory, is not a fact from this query. Those names can only come from parameters, an older turn, or a guess. Either the query returns an Other aggregate, or the answer says that the remaining customer names were not returned.
Failures worth measuring include naming a member outside the result, describing Other as an exact roster, presenting a tie-break as a unique rank, and repeating the previous board after permissions change. The NIST AI Risk Management Framework treats measurement and governance as part of managing generative systems. The summary, chart title, and export should carry the same truncation note. The body cannot admit a partial list while the title says all customers.
Two implementation paths, and when each applies
Path one is a strict cut in a relational engine. Aggregate to customer grain, sort by a unique key, take N rows, and compute the total and Other with the same filters. It fits screens, print layouts, and exports that must stay at N rows. Use it when the business accepts ties broken by a stable key and the answer names that key. Without a unique order, PostgreSQL documentation says the subset is unpredictable, and this path does not hold.
Path two keeps boundary ties. Use a rank function, or a tie-inclusive function such as DAX TOPN, allow more than N rows, and start Other after the tie group. It fits bonuses, quotas, and public lists where cutting a tie would be disputed. The interface must allow a variable row count and say that ties are included and how many rows came back. Do not mix the paths: do not feed tie-inclusive rows into an Other formula written for a strict N, or conservation fails.
Counterexamples, acceptance, and the AskTable.ai boundary
Counterexamples: LIMIT ten with no ORDER BY; sorting by amount alone and ignoring ties; displayed rows that do not add back to the authorized total, with no Other row; a company-wide cut followed by a regional filter whose residual reveals out-of-scope scale; a model that names customers the query never returned. Acceptance checks that, at one watermark, displayed rows plus Other equal the authorized total, that the tie policy and tie-break key are visible, and that the same snapshot returns the same members.
Also check the policy for nulls and test accounts, that a follow-up about Other runs a new query instead of continuing the prose, that every customer name in the narrative appears in the result, and that a permission change recomputes both the board and Other. AskTable.ai can be a candidate entry point for enterprise natural-language queries that turn “top ten customers” into a controlled query with an Other bucket. This article does not show that the current release already switches between a strict N and tie-inclusive cuts, filters before truncation, checks conservation, or stops the model from completing the tail. Verify those behaviors against the actual configuration, using the organization’s own tie and permission cases.
Public references
Ready to help your team start?
Talk through a real scenario and see how AskTable.ai can fit your business.
Book a demo