1 2 Previous Next 22 Replies Latest reply: Apr 5, 2013 7:48 AM by Dave Rabone RSS

    Distinct records based on condition within a table

    user555994
      Hello PL/SQL Gurus/experts,

      I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version
      I have following table -

      Note - Table don't have any primary key on Order_ID -
      DROP TABLE T;
      create table T(Order_ID,Active_Flg) as select
      '201002', 'Y' from dual union all select
      '201002', '' from DUAL union all select
      '201003', '' from dual union all select
      '201004', 'Y' from DUAL union all select
      '201004', '' from dual union all select
      '201005', '' from DUAL ;
      I want to fetch those Order_ID which have Active-Flg as NULL and don't have the entry for Active_Flg=Y

      If use the following then it returns the distinct Order_ID but not the expected one -
      SELECT DISTINCT ORDER_ID FROM T WHERE ACTIVE_FLG IS NULL;
      Result -
      ORDER_ID
      ------
      201004
      201002
      201003
      201005
      Kindly help.....
        1 2 Previous Next