# how determine the best path that satisfies a condition

``````declare @flat table
(
id int not null,
percentage int not null,
value float not null
)

insert into @flat (id, percentage, value)
select 1,10,0.333
union all
select 1,20,0.4
union all
select 2, 50, 0.5
union all
select 2, 70, 0.67
union all
select 3, 10, 0.125
union all
select 3, 40, 0.325
``````

having this sample data, how could i determine the rows that maximize the sum of the values ? Having the following conditions:

• one percentage per id only
• the sum of the percentages must be 100

there is two possible solutions:

``````-- id percentage  value     sum
--  1     10      0.333    0.333
--  2     50      0.5      0.833
--  3     40      0.325    1.158

-- id percentage  value     sum
--  1     20      0.4       0.4
--  2     70      0.67      1.070
--  3     10      0.125     1.195
``````

but it matters only the last one.

-
What did you try? – Colin 't Hart Feb 26 '14 at 16:16
i thought i could use something similar like "the coin change" algorithm, where i was retrieving percentages until 0 reached using a recursive cte..no success. using `code` select id, (select top 1 percentage from @flat as t2 where t2.id=t1.id order by percentage desc) as percentage from @flat as t1 group by id i don't know how to force a running total percentage of 100 – rezanov Feb 26 '14 at 16:34