1 2 Previous Next 22 Replies Latest reply: Apr 5, 2013 7:48 AM by Dave Rabone Go to original post RSS
      • 15. Re: Distinct records based on condition within a table
        jeneesh
        I think this will be the simplest one:
        select Order_ID,max(Active_Flg) flg
        from t
        where nvl(active_flag,'Y') = 'Y'
        group by order_id
        having max(Active_Flg) is null;
        • 16. Re: Distinct records based on condition within a table
          Dave Rabone
          user555994 wrote:
          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
          I like code which reflects the problem statement as closely as possible ...
          select order_id from t where active_flg is null
          minus
          select order_id from t where active_flg = 'Y';
          • 17. Re: Distinct records based on condition within a table
            Karthick_Arp
            I like code which reflects the problem statement as closely as possible ...
            select order_id from t where active_flg is null
            minus
            select order_id from t where active_flg = 'Y';
            The cost is table being scanned twice!!
            • 18. Re: Distinct records based on condition within a table
              jeneesh
              Dave Rabone wrote:
              user555994 wrote:
              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
              I like code which reflects the problem statement as closely as possible ...
              select order_id from t where active_flg is null
              minus
              select order_id from t where active_flg = 'Y';
              But why do you want to do two scans on the same table..?

              What if there are millions of records in the table?
              • 19. Re: Distinct records based on condition within a table
                Dave Rabone
                It's a solution to the problem as posed, not the one we imagine it might actually be.

                I agree analytics rock'n roll, but they aren't necessarily easily understood.

                The other solution which wasn't suggested which is also very easily understood is
                select distinct t1.order_id 
                from t t1
                where t1.active_flg is null
                and not exists ( select null 
                                        from t t2 
                                        where t2.order_id = t1.order_id 
                                        and t2.order_id = 'Y' );
                Just showing OP something different to add to their "toolkit".
                • 20. Re: Distinct records based on condition within a table
                  Karthick_Arp
                  Dave Rabone wrote:
                  It's a solution to the problem as posed, not the one we imagine it might actually be.

                  I agree analytics rock'n roll, but they aren't necessarily easily understood.
                  A feature not being understood properly by someone is not a reason acceptable for not using it.

                  I would like to totally disagree with the idea of scanning a table twice for what so ever the reason it may be. With such SQL in place the application will never scale well.
                  • 21. Re: Distinct records based on condition within a table
                    Dave Rabone
                    Maybe the optimizer in version 13s (s for set oriented) will handle that :-)
                    • 22. Re: Distinct records based on condition within a table
                      Dave Rabone
                      >
                      A feature not being understood properly by someone is not a reason acceptable for not using it.
                      >
                      Agree completely ... but it is a reason why they should not use it until they do understand it. I just felt that the responses to what was a simple question with a very naive attempted solution jumped straight into fairly advanced ideas. Hopefully they inspire the OP to go and study.

                      The idealist in me still says that SQL is a declarative language however. One would hope that primitive set operations could be optimized as well as some of the pseudo-procedural uses of analytics.
                      1 2 Previous Next