Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    13

    Question Unanswered: Find the list of X for which there is only one Y?

    I am trying to find an SQL query that will return a list of items in column X that only appear once, or more precisly only have 1 corresponding Y (X is key). For example:

    Table (R):
    X | Y
    -----
    0 a
    1 a
    1 b
    2 c
    2 d
    2 e

    In this case, I would expect to only get back "0" because it only has one corresponding Y value (a), whereas "1" has two (a & b) and "2" has 3 (c & d & e) ... etc....

    Any help would be greatly appreciated...
    Thanks,

  2. #2
    Join Date
    Jun 2008
    Posts
    7
    Assuming your table is called R and your columns are x and y:

    Code:
    select x from 
    (select x, count(distinct y) nums from R group by x) y_count 
    where nums = 1;

  3. #3
    Join Date
    Jan 2006
    Posts
    13

    Question

    topcat2: thanks for the information. Quick question, do you know how to convert this to Relational Algebra?

Posting Permissions

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