how to select values that sum up to 60% of the tot

Question

assume I have a table of this kind:

A B 3
C D 1
E F 2
G H 4

The sum of the last column is 10, and I want the biggest values that sum up to at least 60% of the total value. So, in this case, it will return

G H 4
A B 3

It goes up to 70% but if only the 1st value was selected, it will only go up to 40%. Even though there could be a combination that will return exactly 60%, we want to take the largest numbers.

So, I think I know how to sort the values from biggest to smallest and how to sum up all the values, but I don't know how to then take only lines that sum up to 60%.

Answer 1

I think this gives you the correct result. Need to work with a temporary table though, not sure if this can be avoided.

DECLARE @total bigint

select @total = SUM(value) from SampleTable

select st.*, 
convert(decimal(10,2), (select SUM(value) from SampleTable st2 where st2.Value >= st.Value))/@total as percentage
into #temptable
from sampletable st

select * from #temptable 
where Value >= (select max(Value) from #temptable where percentage >= 0.6)

Answer 2

--save the whole sum into a variable
summa = select sum(val) from sometable;

select * 
  from sometable o 
 where (
        select sum(val) 
          from sometable i 
         where i.val <= o.val
       ) >= 0.6*summa;