This discussion is archived
6 Replies Latest reply: Nov 23, 2012 12:42 AM by ranit B RSS

Valid Data Result

user11936261 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    what is the data volume in your table?

    ...Vivek
  • 5. Re: Valid Data Result
    sukhijank Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points