I want to keep top n rows in my query result based on the condition of one sum column in my table. I have added the row number and partition by clauses, but stuck at this step.

I have written the query below

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
       

It gives me the result(a sample) below:

ID  WEIGHT  ACC      ROW_NUMBER SUM_WEIGHT
1   1       157691    1           1
2   0.95    338897    1           0.95
3   1       315356    1           1
4   1       314455    1           2
4   1       347079    2           2
5   0.2     297082    1           0.3
5   0.1     356082    2           0.3

What I want my query to return is that "if sum_weight>1 keep the rownumber 1 only else keep all rows for the ID", as below:

ID  WEIGHT  ACC       ROW_NUMBER    SUM_WEIGHT
1   1       157691    1             1
2   0.95    338897    1             0.95
3   1       315356    1             1
4   1       314455    1             2
5   0.2     297082    1             0.3
5   0.1     356082    2             0.3

CodePudding user response：

Wrap the query in an outer query and filter there:

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;

Which, for the sample data:

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;

Outputs:

fiddle