sagar43
sagar43

Reputation: 3464

SQL - Delete random rows where sum is equal to given number

I want to get some random rows from the given table where the sum is 250(this can be changed). I don`t need to 100% accurate but can be approximate. Like for 250 (1,5,7)

Note:- My table has huge data.

id | amount|
1  |   96 |
2  |   0.63 |
3  |   351.03 |
4  |   736| 
5  |   53 |
6  |   39 |
7  |   105 |
8  |   91 |

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

For any specific numbers, you can return the closest sum using logic with joins. For instance for 3 numbers -- as in your example:

select t1.*, t2.*, t3.*
from t t1 join
     t t2
     on t1.id < t2.id join
     t t3
     on t2.id < t3.id
order by abs(250 - (t1.amount + t2.amount + t3.amount))
fetch first 1 row only;

Note that fetch first is standard SQL. Some databases spell it differently, for instance as limit or select top.

Upvotes: 2

Related Questions