Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Unanswered: SQL Query -- Help

    Hi!
    How can I filter the complete record if there is existence of 1. Please see the example below:

    E.g;

    id value
    100 1
    100 2
    100 3

    104 3
    104 3

    106 2
    106 2

    108 3
    108 4
    118 1

    The SQL result set should be :

    104 3
    104 3

    106 2
    106 2

    Thanks in adcance.
    namoh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Query -- Help

    Do you mean:

    select id, value
    from t
    where id in (select id from t group by id having count(distinct value)=1);

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Or do you mean:

    SELECT id, value
    FROM t
    WHERE id NOT IN
    ( SELECT DISTINCT id FROM t
    WHERE value = 1 );

    JoeB

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: SQL Query -- Help

    Originally posted by andrewst
    Do you mean:

    select id, value
    from t
    where id in (select id from t group by id having count(distinct value)=1);
    Hi Andrew, You are wright but need small change,


    select id, value
    from t
    where id NOT in (select id from t group by id having count(distinct value)=1);
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Feb 2004
    Posts
    16

    Re: SQL Query -- Help

    Originally posted by andrewst
    Do you mean:

    select id, value
    from t
    where id in (select id from t group by id having count(distinct value)=1);
    Thank you for your reply. Actually it is the other way around(NOT IN):

    select id, value
    from t
    where id not in (select id from t group by id having count(distinct value)=1);

    The query takes lot of time (almost 20 mins.) to execute.

    Regards,
    namoh

  6. #6
    Join Date
    Feb 2004
    Posts
    16
    Originally posted by joebednarz
    Or do you mean:

    SELECT id, value
    FROM t
    WHERE id NOT IN
    ( SELECT DISTINCT id FROM t
    WHERE value = 1 );

    JoeB
    Thanks a lot for your help. This works and the response time is faster.
    I appreciate your help.

    Regards,
    namoh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •