Jump to content


This topic is now archived and is closed to further replies.


Selecting rows based on the sum of a field

Recommended Posts

I'm not sure if I have worded the topic correctly, but, what I am trying to do is this...

For the sake of keeping things simpler, my database has three fields: id, number and priority. Number can hold one of four values: 0, 1, 2 and 3. I'd like to order my results by `priority` in ascending order. I only want to select enough rows so that the sum of the `number` field within the selected rows is equal to, or less than three.

For example, idealy it would return one row with the `number` field holding '3', or return two rows with one row holding '2' in the `number` field and the other holding '1', or it would return three rows with each rows `number` field holding '1'.
If there are not enough rows to return the sum of '3', it would return what is available, i.e. one row with '2', or two/one row with '1'.

I'm not even sure where to start when writing this query - Does anyone have any ideas, tips or suggestions?

Many thanks.

Share this post

Link to post
Share on other sites
My suggestion would be following: query the table to find out how many rows of each `number` you have, so that you can dynamically determine which combination of rows is optimal.  Then, issue a UNION query with appropriate LIMITs.  Hope that makes sense... I can explain further if need be.  I'm not sure that is the most optimal, but it's probably the most efficient.

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.