How to choose values ​​that add up to 60% of the total

Question

Suppose I have a table of this type:

AB 3
CD 1
Ef 2
Gh 4

The sum of the last column is 10, and I need the largest values, the sum of which is at least 60% of the total value. So in this case it will return

Gh 4
AB 3

It reaches 70%, but if only the first value is selected, it will increase to 40%. Despite the fact that there may be a combination that returns exactly 60%, we want to take the largest numbers.

So, I think that I know how to sort the values ​​from the largest to the smallest and how to sum all the values, but I do not know how then to take only the lines summing up to 60%.

Answer 1

--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; 

Answer 2

I think this gives the correct result. I need to work with a temporary table, but I'm not sure that 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)