By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (ID, WEIGHT, ACC) AS
SELECT 1, 1, 157691 FROM DUAL UNION ALL
SELECT 2, 0.95, 338897 FROM DUAL UNION ALL
SELECT 3, 1, 315356 FROM DUAL UNION ALL
SELECT 4, 1, 314455 FROM DUAL UNION ALL
SELECT 4, 1, 347079 FROM DUAL UNION ALL
SELECT 5, 0.2, 297082 FROM DUAL UNION ALL
SELECT 5, 0.1, 356082 FROM DUAL;
7 rows affected
SELECT *
FROM (
SELECT ID,
WEIGHT,
ACC,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID, WEIGHT asc, ACC asc)
as ROWNUMBER,
SUM (WEIGHT) OVER (PARTITION BY ID) as SUM_WEIGHT
FROM TABLE1
)
WHERE rownumber = 1
OR sum_weight <= 1;
| ID | WEIGHT | ACC | ROWNUMBER | SUM_WEIGHT |
|---|---|---|---|---|
| 1 | 1 | 157691 | 1 | 1 |
| 2 | .95 | 338897 | 1 | .95 |
| 3 | 1 | 315356 | 1 | 1 |
| 4 | 1 | 314455 | 1 | 2 |
| 5 | .1 | 356082 | 1 | .3 |
| 5 | .2 | 297082 | 2 | .3 |