Jump to content


Photo

Selecting rows based on the sum of a field


  • Please log in to reply
1 reply to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 01 October 2006 - 09:02 PM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 October 2006 - 11:59 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users