CODEWITHSUNDEEP
sqlalgorithm
Ajay Singh Beniwal
Ajay Singh Beniwal

Reputation: 19037

SQL Query to find total weight

I have 30 records in a table having column "box number" and "weight". I have to find 20 boxes out of 30 boxes whose sum of weight is nearest to 1000 kg.

Upvotes: 3

Views: 1753

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The way you would solve this in SQL is not recommended, and may not even be feasible on any machine. But, you can express the brute force solution as:

select box1.id, box2.id, . . . ,
       (box1.weight + box2.weight + . . .)
from box box1 join
     box box2
     on box2.id > box1.id join
     box box3
     on box3.id > box2.id join
     box box4
     on box4.id > box3.id
     . . .
     box box20
     on box20.id > box19.id
order by abs(1000 - (box1.weight + box2.weight + . . .))

The "on" conditions guarantee that you are looking at each set of boxes once, ordered by their unique ids. The use of "join" rather than "left join" ensures that you end up with exactly 20 boxes.

Upvotes: 0

Klark
Klark

Reputation: 8280

SQL Server:

select TOP 20 box_number 
from t_boxes 
group by box_number 
order by ABS(SUM(box_weight) - 1000) ASC

Edit: In case I misunderstood your question and you actually want to solve knapsack problem. This is something that you really should do in SQL, but you can read this article with very appropriate name: And now for a completely inappropriate use of SQL Server.

Upvotes: 2

bpgergo
bpgergo

Reputation: 16037

I suggest you read about the Knapsack problem

Upvotes: 5

Related Questions