Using the analytic function ROW_NUMBER(), unique values can be selected as shown in the query below. WITH q_dup AS (SELECT MOD(level,2)+1 dbt_idx, TO_CHAR(sysdate+MOD(level,2),'yyyymmdd') dbt_date_id FROM DUAL CONNECT BY LEVEL <= 6), w_unique AS (SELECT dbt_idx, dbt_date_id, ROW_NUMBER() OVER (PARTITION BY dbt_idx ORDER BY dbt_date_id) row_val FROM q_dup) SELECT dbt_idx, dbt_date_id FROM w_unique WHERE row_val = 1; Unique rows of data can be inserted into the table using the query below restricting the data to row_val of 1 to create unique data set. The row_number window setup can be customzied based on data being inserted. INSERT INTO dbt_nf11g_demo (dbt_idx, dbt_date_id) WITH q_dup AS (SELECT MOD(level,2)+1 dbt_idx, TO_CHAR(sysdate+MOD(level,2),'yyyymmdd') dbt_date_id FROM DUAL CONNECT BY LEVEL <= 6), w_unique AS (SELECT dbt_idx, dbt_date_id, ROW_NUMBER() OVER (PARTITION BY dbt_idx ORDER BY dbt_date_id) row_val FROM q_dup) SELECT dbt_idx, dbt_date_id FROM w_unique WHERE row_val = 1; Use of Oracle 11g hint - IGNORE_ROW_ON_DUPKEY_INDEX
23857