Selecting rows based on the sum of a field

[zq29]

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.

[fenway]

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.