#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    45
    Rep Power
    0

    Distinct on multiple fields


    Hi,

    One Liner:
    Select all columns from a table except primary key such that the columns do not repeat all at the same time.

    Description:
    I have a table 'testTable' such as:



    Col1 is the key. I want to SELECT all the columns provided that Col2, Col3 and Col4 do not repeat.
    One or two of them can repeat at a time. But All of them should not ever repeat.

    In case of above table the query should return only row 1,2 and 4. It shouldn't return row 3 because in row three all the three columns are repeating (matching with row 1).

    I have tried using Distinct and Order By. But they either take 1 column or all the columns.

    Any help or suggestion would be highly appreciated
    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    Code:
    select min(col1),
           col2,
           col3,
           col4
      from testTable
     group
        by col2,
           col3,
           col4
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2009
    Posts
    45
    Rep Power
    0
    Originally Posted by swampBoogie
    Code:
    select min(col1),
           col2,
           col3,
           col4
      from testTable
     group
        by col2,
           col3,
           col4

    Thanks a lot , it worked. Can you please explain why do we need to use MIN function here?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,146
    Rep Power
    4274
    Originally Posted by adi.shoukat
    Can you please explain why do we need to use MIN function here?
    because you said you wanted col1 = 1 but not col1 = 3

    you could also use MAX or AVG -- the point is, you have to use ~some~ aggregate function if you want a value for col1 returned, because of the GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo