SQL> drop table test_prd purge;

Table dropped.

SQL> create table test_prd(prd_id integer,prd_eff_dt date,prd_cd integer);

Table created.

SQL> insert into test_prd values(1,date'2016-04-01',2569);

1 row created.

SQL> insert into test_prd values(10,date'2016-03-15',2569);

1 row created.

SQL> insert into test_prd values(20,date'2016-04-24',2569);

1 row created.

SQL> insert into test_prd values(31,date'2016-04-15',2569);

1 row created.

SQL> insert into test_prd values(7,date'2016-02-01',2569);

1 row created.

SQL> insert into test_prd values(11,date'2016-02-15',2570);

1 row created.

SQL> insert into test_prd values(67,date'2016-01-24',2570);

1 row created.

SQL> insert into test_prd values(19,date'2016-03-15',2570);

1 row created.

SQL> insert into test_prd values(18,date'2016-03-01',2570);

1 row created.

SQL> insert into test_prd values(14,date'2016-03-11',2573);

1 row created.

SQL> insert into test_prd values(98,date'2016-03-24',2573);

1 row created.

SQL> insert into test_prd values(89,date'2016-02-11',2573);

1 row created.

SQL> insert into test_prd values(56,date'2016-01-24',2573);

1 row created.

SQL> insert into test_prd values(45,date'2016-01-23',2573);

1 row created.

SQL> select * from test_prd;

    PRD_ID PRD_EFF_D     PRD_CD
---------- --------- ----------
         1 01-APR-16       2569
        10 15-MAR-16       2569
        20 24-APR-16       2569
        31 15-APR-16       2569
         7 01-FEB-16       2569
        11 15-FEB-16       2570
        67 24-JAN-16       2570
        19 15-MAR-16       2570
        18 01-MAR-16       2570
        14 11-MAR-16       2573
        98 24-MAR-16       2573
        89 11-FEB-16       2573
        56 24-JAN-16       2573
        45 23-JAN-16       2573

14 rows selected.

SQL> select prd_id,prd_eff_dt,prd_cd,
  2        row_number()over(partition by prd_cd order by prd_eff_dt desc) cd_rank
  3  from test_prd;

    PRD_ID PRD_EFF_D     PRD_CD    CD_RANK
---------- --------- ---------- ----------
        20 24-APR-16       2569          1
        31 15-APR-16       2569          2
         1 01-APR-16       2569          3
        10 15-MAR-16       2569          4
         7 01-FEB-16       2569          5
        19 15-MAR-16       2570          1
        18 01-MAR-16       2570          2
        11 15-FEB-16       2570          3
        67 24-JAN-16       2570          4
        98 24-MAR-16       2573          1
        14 11-MAR-16       2573          2
        89 11-FEB-16       2573          3
        56 24-JAN-16       2573          4
        45 23-JAN-16       2573          5

14 rows selected.

SQL> select *
  2  from (
  3  select prd_id,prd_eff_dt,prd_cd,
  4        row_number()over(partition by prd_cd order by prd_eff_dt desc) cd_rank
  5  from test_prd
  6  )
  7  where cd_rank <=2;

    PRD_ID PRD_EFF_D     PRD_CD    CD_RANK
---------- --------- ---------- ----------
        20 24-APR-16       2569          1
        31 15-APR-16       2569          2
        19 15-MAR-16       2570          1
        18 01-MAR-16       2570          2
        98 24-MAR-16       2573          1
        14 11-MAR-16       2573          2

6 rows selected.

SQL>