Keep same table structure but only MAX values-CodePudding

Hi all, How can I, in the most efficient way, keep only the records with the max weights values per color but same table structure (all columns) without using subselects ? I was trying: max(over partition by colour) but I don't know how to condition further. (not sure if this is the best way) Thanks!

CodePudding user response：

If you mean to delete those rows: In CTE Block the record with 1 value shows MAX weight. So When Joining main table with CTE adding extra condition RN = 1, cause that other value that RN is not equal 1 become NULL; and there are the record should be deleted and max value remain in table.

With CTE As (
 Select BRICK_ID, Row_Number() Over (Partition By color, order by Weight DESC) RN
  From table_name
  )
DELETE tbl
  FROM CTE 
    RIGHT JOIN table_name tbl ON CTE.BRICK_ID = tbl.BRICK_ID AND RN = 1
  WHERE CTE.BRICK_ID IS NULL

And if you want to move the records with max weight to another table with same structure, use this one:

With CTE As (
 Select BRICK_ID, Row_Number() Over (Partition By color, order by Weight DESC) RN
  From table_name
  )
Select tbl.*
into new_table_name
  FROM CTE 
    INNER JOIN table_name tbl ON CTE.BRICK_ID = tbl.BRICK_ID

CodePudding user response：

You can delete rows and keep the max Weight for each colour group by using an updatable CTE, such as:

with delme as (
  select *, row_number() over(partition by colour order by weight desc) rn
  from Yourtable
)
delete from delme where rn > 1;