Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Location
    Germany, near Munich
    Posts
    4

    Unanswered: Simple (?) SQL question

    Let's assume a simple table with three integer fields x,y,z.
    I want to retrieve all values x, where the corresponding pair of (y,z) is unique in the table, e.g. for values
    x y z
    1 3 4
    2 3 5
    3 4 7
    4 4 7
    5 9 2

    I would like to get 1,2,5, since for x=3,4 I have a duplicate y,z pair.

    I know how to do this with a (self) join or a subquery, but I would like to
    do it without these, and if possible without Oracle-proprietary stuff, pure SQL...
    Performance is important ...
    Thanks!
    Thomas

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    When did a sub-query become impure SQL? Sounds like the right solution here to me.

    -cf

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >without Oracle-proprietary stuff
    Then why are you asking in an Oracle forum?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    SELECT X
    FROM MY_TABLE
    WHERE (Y,Z) IN
    (SELECT Y,Z
    FROM MY_TABLE
    GROUP BY Y,Z
    HAVING COUNT(*) = 1);

    Pure sql, no oracle specific stuff. You can't do this without a sun-query, you have to find the unique pairs before you can pull out the x values.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I am still looking for a solution without a sub-query or joins (and therefore, code). Bummer, every DB version out there has joins..

  6. #6
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    How about this?
    Code:
    SELECT x
    FROM vals
    GROUP BY y, z
    HAVING COUNT(DISTINCT(x)) = 1
    If x is your primary key column, then you don't need the DISTINCT. It's been a long time since I've used Oracle, but this seems to work in MySQL and Sybase.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    In Oracle, that'll give you a:

    Code:
    ORA-00979: not a GROUP BY expression
    -cf

  8. #8
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Quote Originally Posted by chuck_forbes
    In Oracle, that'll give you a:

    Code:
    ORA-00979: not a GROUP BY expression
    -cf
    Again, sorry for my current lack of Oracle knowledge, just trying to help out. Checked that error and it seems like you could do this?
    Code:
    SELECT MAX(x) AS x, y, z
    FROM vals
    GROUP BY y, z
    HAVING COUNT(DISTINCT(x)) = 1
    Since only one value of x is going to be found, you can use any aggregate function to include it in the select clause. If that (or a similar tweak) doesn't work, I'll stop flooding this post . It's just an interesting problem.

  9. #9
    Join Date
    Feb 2005
    Location
    Germany, near Munich
    Posts
    4
    @chuck_forbes: No, I didn't want to call sub-queries "impure", I'm just using a subquery, but it's way too slow (~ 50,000 entries in my table, query takes ~45 min.). That was just my first idea I had:

    select distinct(x) from TABLE T1
    where (select count(*) from TABLE where y = T1.y and z = T1.z ) = 1

    That's a pretty good description of the problem, but it's not a good solution.

    @anacedent: Well, I was in a hurry yesterday, and I didn't scroll down to see that there is also a SQL forum ..., but anyway I'm working on Oracle, and I thought I might get at least a good Oracle-specific solution if no pure SQL would work fast enough ... and, well, I know that the Oracle people are the smartest

    @beilstwh: First prize for you, solution works and is fast enough ... I didn't mention that there might be several (y,z) pairs for one value of x, so I just had to add a "distinct" to your query,
    i.e. "select distinct(x) from ...".

    @JMartinez: I assume that there is no solution without subquery or join, so don't spent too much time on this...

    Thanks to you all ...

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    You might also try some different versions of the subquery approach

    Code:
    select distinct x
    from tab t1
    where (y,z) in (select t2.y, t2.z
                    from tab t2
                    group by t2.y, t2.z
                    having count(*) = 1)
    
    select distinct x
    from tab t1
    where (y,z) not in (select t2.y, t2.z
                        from tab t2
                        group by t2.y, t2.z
                        having count(*) > 1)
    
    select distinct x
    from tab t1,
        (select t2.y, t2.z
         from tab t2
         group by t2.y, t2.z
         having count(*) = 1) distinct_val
    where t1.y = distinct_val.y and
          t1.z = distinct_val.z
    Although it sounds like you already found the approach you're after, if the above are also still slow, and composite index on (y,z) might speed up the subqueries considerably.

    -cf

Posting Permissions

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