You'll have to use an advanced query using the Union syntax, something like the following.


Note that the syntax might not be 100% correct as I didn't test it.  I would try each Select separately to make sure it works as expected and then combine them using the Union syntax as shown.

Hope this helps,
Curt

Hi Martijn.
Let's rewrite your business logic, but speaking a little math lingo.

Your products have an Amount field, and the aggregate will run in a context where you have a RequestedAmount. Both are integers greater or equal to zero.

- First show all the products with amount is equal to the requested amount.
This is a case which Mod(Amount, RequestedAmount) = 0, and Amount = RequestedAmount.

- Secondly show all the products which have an amount that’s equal to n*requested amount (n is an integer).
In this case, Mod(Amount, RequestedAmount) = 0 and Amount > RequestedAmount.

- At last all other amount sort from small too big.
These are the cases where Mod(Amount, RequestedAmount) > 0.

Now, in a first attempt you could query all products, and order by Mod(Amount, RequestedAmount) *. This will make the first two cases appear on top, and the third case below. But it won't distinguish between the first two cases.

You can add a second order by Amount. It will make the products with Amount equal to RequestedAmount to appear on top, and those with greater amounts below, thus distinguishing the first two cases.

This is just a start, though. To get your requirements exactly right, you will need to solve the following situations:
- Products whose Amount is zero will be the first results on the list, and from your requirements they should be on the top of the third set.
- Products on the third set will be sorted first by Mod(Amount, RequestedAmount). This will mean, in your example, that a box with 13 nails will appear first than a box with 5 nails.

If you cannot relax your requirements, get back to me, and we will make things a little more complex.

---
* To order by a function, you'll need to add a calculated column, and then order by this new column.

Hi Leonardo,

When I try to do the Mod(Amount, RequestedAmount)  in a calculated column I get the following message:
'mod' function cannot be executed in the database, so it can't receive any attributes from the aggregate as parameter.

The way you described it would match my requirements though.

Oh well, that goes for not testing it! You will have to resort to an advanced query then. In this case the solution given by Curt seems more appropriate and easier to understand.