zq29 Posted October 1, 2006 Share Posted October 1, 2006 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. Link to comment https://forums.phpfreaks.com/topic/22685-selecting-rows-based-on-the-sum-of-a-field/ Share on other sites More sharing options...
fenway Posted October 2, 2006 Share Posted October 2, 2006 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. Link to comment https://forums.phpfreaks.com/topic/22685-selecting-rows-based-on-the-sum-of-a-field/#findComment-102765 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.