Results 1 to 4 of 4

Thread: Exclusive Joins

  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: Exclusive Joins

    First post, so be gentle.

    I'm guessing this is a common problem, but my SQL inexperience is showing here. I have a set of rows, within which there are pairs which differ only by one column. I basically want to select a dataset, but not get one of each of the pairs. I have solved this in two different ways that work, but both look messy and I feel there must be a simpler way of doing it. What I want is something like :

    Select field1,field2,field3 from tab
    where
    field3=1 or
    (field3=0 and (rows not in the data set returned by field3=1))

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @tab table (
       field1 char(1)
     , field2 char(1)
     , field3 char(1)
    )
    
    INSERT INTO @tab (field1, field2, field3) VALUES ('a', 'b', 'c')
    INSERT INTO @tab (field1, field2, field3) VALUES ('a', 'b', 'x')
    INSERT INTO @tab (field1, field2, field3) VALUES ('a', 'b', 'y')
    -- Which of these 3 do you want?
    
    INSERT INTO @tab (field1, field2, field3) VALUES ('p', 'q', 'r')
    INSERT INTO @tab (field1, field2, field3) VALUES ('p', 'q', 'r')
    -- These are the same, so arbitrarily return one result
    
    INSERT INTO @tab (field1, field2, field3) VALUES ('h', 'i', 'j')
    INSERT INTO @tab (field1, field2, field3) VALUES ('u', 'v', 'w')
    
    -- Simplest solution; return the max or min field3
    SELECT field1
         , field2
         , Min(field3) As field3
         --, Max(field3) As field3
    FROM   @tab
    GROUP
        BY field1
         , field2
    
    -- Using windowed functions
    SELECT field1
         , field2
         , field3
    FROM   (
            SELECT field1
                 , field2
                 , field3
                 , Row_Number() OVER (PARTITION BY field1, field2 ORDER BY field3) As row_num
            FROM   @tab
           ) As x
    WHERE  row_num = 1
    George
    Home | Blog

  3. #3
    Join Date
    May 2010
    Posts
    3
    Quote Originally Posted by gvee View Post
    Code:
    DECLARE @tab table (
       field1 char(1)
     , field2 char(1)
     , field3 char(1)
    )
    
    INSERT INTO @tab (field1, field2, field3) VALUES ('a', 'b', 'c')
    INSERT INTO @tab (field1, field2, field3) VALUES ('a', 'b', 'x')
    INSERT INTO @tab (field1, field2, field3) VALUES ('a', 'b', 'y')
    -- Which of these 3 do you want?
    
    INSERT INTO @tab (field1, field2, field3) VALUES ('p', 'q', 'r')
    INSERT INTO @tab (field1, field2, field3) VALUES ('p', 'q', 'r')
    -- These are the same, so arbitrarily return one result
    
    INSERT INTO @tab (field1, field2, field3) VALUES ('h', 'i', 'j')
    INSERT INTO @tab (field1, field2, field3) VALUES ('u', 'v', 'w')
    
    -- Simplest solution; return the max or min field3
    SELECT field1
         , field2
         , Min(field3) As field3
         --, Max(field3) As field3
    FROM   @tab
    GROUP
        BY field1
         , field2
    
    -- Using windowed functions
    SELECT field1
         , field2
         , field3
    FROM   (
            SELECT field1
                 , field2
                 , field3
                 , Row_Number() OVER (PARTITION BY field1, field2 ORDER BY field3) As row_num
            FROM   @tab
           ) As x
    WHERE  row_num = 1
    Thanks. One of my efforts was using the nested select in your second example, but as the actual select is rather more complex than the example I gave, it ends up being messy, as I said. The Min/Max idea might be something though - I need to think about that.

  4. #4
    Join Date
    May 2010
    Posts
    3
    Min/max seems to work a treat - code looks nice and tidy now (and works too, which is always a bonus !!)

    Thanks.

Posting Permissions

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