Thursday, July 30, 2015

In a project, I need to remove rows with lowest or highest values within groups. I used the following table to illustrate the problem. For grp 10, rows with val 1 and 4 need to be removed and for grp 11, rows with val 5.5 and 9.6.

delete from tbl_x2 a
where a.rowid in
(
select rid from
(
with tbl as (
select b.rowid rid,
row_number() over(partition by grp order by val) rnk,
row_number() over(partition by grp order by val desc) rnkr
from tbl_x2 b)
select rid from tbl where rnk=1 or rnkr=1
)
);

The following query shows that rows with lowest or highest values within groups are removed.

The trick is to use row_number() function to generate ranks or reverse ranks based on val for each grp. For example, the following query show all rows in the original table and the ranks (rnk) and reversed ranks (rnkr). The lowest val has a rank of 1 and the highest val has a reversed rank of 1.