I'm stuck in select query to get best match from table like this:

I'm trying to find best 6 records with the lowest sum of B, but there's additional condition that sum of A need to be higher than 150.

This table is sorted by B, and the best results is on the top. So sum of B from 6 top results is 21, but in this case sum of A is 135 which is lower than 150. So record nb 6 should be excluded and the next nb 7 should be included. So the best result is sum of B = 22 from records 1-5 and 7, while sum of A is 151, which is enough.

I used rank function with partition and order but every time query returned 0 records. Is there any possibility to exclude this 6th record and take 7 to the result, so sum of A will be > 150?

CodePudding user response：

This seems to be a variation on the knapsack problem, which is NP-Complete, and the easiest (to code) solution is just brute-forcing every permutation.

Given that you have a fixed number of items, you can do this by self-joining

SELECT TOP (1)
  *
FROM Items i1
JOIN Items i2 ON i2.Name > i1.Name
JOIN Items i3 ON i3.Name > i2.Name
JOIN Items i4 ON i4.Name > i3.Name
JOIN Items i5 ON i5.Name > i4.Name
JOIN Items i6 ON i6.Name > i5.Name
CROSS APPLY (VALUES (
    i1.A   i2.A   i3.A   i4.A   i5.A   i6.A,
    i1.B   i2.B   i3.B   i4.B   i5.B   i6.B
)) v(TotalA, TotalB)
WHERE TotalA > 150
ORDER BY
  TotalB;

Name in this case is just a proxy for some unique column

db<>fiddle

There may be more efficient solutions, depending on your exact problem.