6 Replies Latest reply: Nov 23, 2012 2:42 AM by ranit B RSS

    Valid Data Result

    user11936261
      Hi ,

      I am trying to get the below result from the table which looks like below. In this I need to restrict 6 value in column c3 for the same bunch of c1 and c2.

      Restrict means I need to restrict the whole group having c3 value =6 and display remaing values. Pls help me out.

      c1 c2 c3
      123 21-Jul-12 4
      123 21-Jul-12 4
      125 23-Jul-12 6
      126 24-Jul-12 7
      126 24-Jul-12 7
      126 24-Jul-12 8
      126 24-Jul-12 10
      126 27-Jul-12 13
      126 27-Jul-12 1
      127 25-Jul-12 6
      127 25-Jul-12 12
      127 25-Jul-12 11
      128 29-Jul-12 6
      128 29-Jul-12 6


      Result:
      c1 c2 c3
      123 21-Jul-12 4
      123 21-Jul-12 4
      126 24-Jul-12 7
      126 24-Jul-12 7
      126 24-Jul-12 8
      126 24-Jul-12 10
      126 27-Jul-12 13
      126 27-Jul-12 1

      Pls help me
      -akh
        • 1. Re: Valid Data Result
          793996
          Select * from your_tab
          where (c1, c2) not in (select c1, c2 from your_tab
          where c3 = 6)

          Is this what you want? If no, please elaborate in restricting condition.

          Thanks,
          Vivek
          • 2. Re: Valid Data Result
            user11936261
            I have first written this query,,,..but its taking long time to execute ?
            Any other way to make it fast
            • 3. Re: Valid Data Result
              hm
              with yourtable as
              (
              select 123 c1, to_date('21-Jul-12','DD-Mon-YY') c2, 4 c3 from dual union all
              select 123, to_date('21-Jul-12','DD-Mon-YY'), 4 from dual union all
              select 125, to_date('23-Jul-12','DD-Mon-YY'), 6 from dual union all
              select 126, to_date('24-Jul-12','DD-Mon-YY'), 7 from dual union all
              select 126, to_date('24-Jul-12','DD-Mon-YY'), 7 from dual union all
              select 126, to_date('24-Jul-12','DD-Mon-YY'), 8 from dual union all
              select 126, to_date('24-Jul-12','DD-Mon-YY'), 10 from dual union all
              select 126, to_date('27-Jul-12','DD-Mon-YY'), 13 from dual union all
              select 126, to_date('27-Jul-12','DD-Mon-YY'), 1 from dual union all
              select 127, to_date('25-Jul-12','DD-Mon-YY'), 6 from dual union all
              select 127, to_date('25-Jul-12','DD-Mon-YY'), 12 from dual union all
              select 127, to_date('25-Jul-12','DD-Mon-YY'), 11 from dual union all
              select 128, to_date('29-Jul-12','DD-Mon-YY'), 6 from dual union all
              select 128, to_date('29-Jul-12','DD-Mon-YY'), 6 from dual
              )
              select * 
              from yourtable
              where (c1,c2) not in (select c1,c2 from yourtable where c3=6);
              • 4. Re: Valid Data Result
                793996
                what is the data volume in your table?

                ...Vivek
                • 5. Re: Valid Data Result
                  sukhijank
                  You can write it in multiple ways. The optimal way will depend on your data distribution
                  with yourtable as
                  (
                  select 123 c1, to_date('21-Jul-12','DD-Mon-YY') c2, 4 c3 from dual union all
                  select 123, to_date('21-Jul-12','DD-Mon-YY'), 4 from dual union all
                  select 125, to_date('23-Jul-12','DD-Mon-YY'), 6 from dual union all
                  select 126, to_date('24-Jul-12','DD-Mon-YY'), 7 from dual union all
                  select 126, to_date('24-Jul-12','DD-Mon-YY'), 7 from dual union all
                  select 126, to_date('24-Jul-12','DD-Mon-YY'), 8 from dual union all
                  select 126, to_date('24-Jul-12','DD-Mon-YY'), 10 from dual union all
                  select 126, to_date('27-Jul-12','DD-Mon-YY'), 13 from dual union all
                  select 126, to_date('27-Jul-12','DD-Mon-YY'), 1 from dual union all
                  select 127, to_date('25-Jul-12','DD-Mon-YY'), 6 from dual union all
                  select 127, to_date('25-Jul-12','DD-Mon-YY'), 12 from dual union all
                  select 127, to_date('25-Jul-12','DD-Mon-YY'), 11 from dual union all
                  select 128, to_date('29-Jul-12','DD-Mon-YY'), 6 from dual union all
                  select 128, to_date('29-Jul-12','DD-Mon-YY'), 6 from dual
                  )
                  select * 
                  from yourtable
                  where (c1,c2) not in (select c1,c2 from yourtable where c3=6);
                  OR
                  select a.c1, a.c2, a.c3 from yourtable a, (
                  select c1, c2 from yourtable
                  minus
                  select c1, c2 from yourtable where c3 = 6) b
                  where a.c1 = b.c1
                  and a.c2 = b.c2
                  OR
                  select c1, c2, c3 from yourtable o
                  where not exists (select 1 from yourtable where c1 = o.c1 and c2 = o.c2 and c3 = 6)
                  OR
                  select c1, c2, c3 from yourtable o
                  where (c1, c2) in (
                  select c1, c2 from yourtable
                  minus
                  select c1, c2 from yourtable where c3 = 6)
                  If all the columns are not null, all queries will correct result. You have to choose which one suits you.
                  • 6. Re: Valid Data Result
                    ranit B
                    with yourtable as
                    (
                    select 123 c1, to_date('21-Jul-12','DD-Mon-YY') c2, 4 c3 from dual union all
                    select 123, to_date('21-Jul-12','DD-Mon-YY'), 4 from dual union all
                    select 125, to_date('23-Jul-12','DD-Mon-YY'), 6 from dual union all
                    select 126, to_date('24-Jul-12','DD-Mon-YY'), 7 from dual union all
                    select 126, to_date('24-Jul-12','DD-Mon-YY'), 7 from dual union all
                    select 126, to_date('24-Jul-12','DD-Mon-YY'), 8 from dual union all
                    select 126, to_date('24-Jul-12','DD-Mon-YY'), 10 from dual union all
                    select 126, to_date('27-Jul-12','DD-Mon-YY'), 13 from dual union all
                    select 126, to_date('27-Jul-12','DD-Mon-YY'), 1 from dual union all
                    select 127, to_date('25-Jul-12','DD-Mon-YY'), 6 from dual union all
                    select 127, to_date('25-Jul-12','DD-Mon-YY'), 12 from dual union all
                    select 127, to_date('25-Jul-12','DD-Mon-YY'), 11 from dual union all
                    select 128, to_date('29-Jul-12','DD-Mon-YY'), 6 from dual union all
                    select 128, to_date('29-Jul-12','DD-Mon-YY'), 6 from dual
                    )
                    SELECT *
                      FROM yourtable x
                      WHERE NOT EXISTS
                              (SELECT 1 FROM yourtable y
                                WHERE y.c1 = x.c1 AND y.c2 = x.c2 AND y.c3 = 6)
                    EXISTS mostly performs better than its peers, when we have lot of options to opt for.
                    Please test and choose.