Select Duplicate Row with Custom Amount Oracle

Question

In a [PERFORMANCE] table, some rows have the same value for the PERFORMANCE_INDICATOR column.

ID | PERFORMANCE_INDICATOR                 | REALISASI |
---------------------------------------------------------
 1 | Compliance of Inventory Data Consumer | 90,91     |
 7 | Compliance of Inventory Data Consumer | 92,22     |
13 | Compliance of Inventory Data Consumer | 93,31     |
 9 | Migrasi ODF to FTM                    | 90,91     |
 3 | Migrasi ODF to FTM                    | 92,22     |
14 | Migrasi ODF to FTM                    | 93,31     |

I want to select just two data every duplicate data exist like this:

ID | PERFORMANCE_INDICATOR                 | REALISASI |
    -----------------------------------------------------
 1 | Compliance of Inventory Data Consumer | 90,91     |
 7 | Compliance of Inventory Data Consumer | 92,22     |
 9 | Migrasi ODF to FTM                    | 90,91     |
 3 | Migrasi ODF to FTM                    | 92,22     |

What SQL statement should I use to find those rows? Thanks for your help.

Your question is very unclear. Please try to be more specific, and show the code you already have. — Classified, Aug 03 '18 at 06:58

score 1 · Accepted Answer · answered Aug 03 '18 at 07:32

Use analytic ROW_NUMBER then filter on it:

with cte as (
    select your_table.*
           , row_number() over (partition by PERFORMANCE_INDICATOR order by ID) as rn
    from your_table
)
select ID, PERFORMANCE_INDICATOR, REALISASI
from cte
where rn <= 2
order by PERFORMANCE_INDICATOR, ID
/

Select Duplicate Row with Custom Amount Oracle

1 Answers1