Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34

    Unanswered: Select DISTINCT with other fields

    I am trying to select all of the fields in my datatbl with distinct email fields.

    I tried this but it does not work?

    sql = "SELECT * FROM (SELECT DISTINCT email FROM datatbl WHERE optin = 'Yes' AND spanish = 'No')"

    Any ideas?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i'm not sql guru but...
    SELECT DISTINCT fieldsThatYouWantToBeDistinct FROM table/query WHERE conditions
    should fly

    your SELECT * FROM (SELECT.....) WITHOUT-A-WHERE
    doesn't seem to do anything useful other than absorb clock cycles

    izy

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Are you saying you want to return only records that do not have an email address repeated ?

  4. #4
    Join Date
    Nov 2002
    Posts
    150

    Re: Select DISTINCT with other fields

    Originally posted by rob7765
    I am trying to select all of the fields in my datatbl with distinct email fields.

    I tried this but it does not work?

    sql = "SELECT * FROM (SELECT DISTINCT email FROM datatbl WHERE optin = 'Yes' AND spanish = 'No')"

    Any ideas?
    This works but can be a killer if the table is huge and/or isn't indexed properly:

    SELECT Table1.*
    FROM Table1
    WHERE 1 = (SELECT COUNT(1) FROM Table1 T1 WHERE T1.Value = Table1.Value)

  5. #5
    Join Date
    Oct 2002
    Location
    Houston, TX
    Posts
    34
    Originally posted by rnealejr
    Are you saying you want to return only records that do not have an email address repeated ?
    No, but you question makes me see the problem. I guess what I wanted was to return all of the records except for duplicate ones. If someone has filled out the form more than once and are listed in the database more than once, I only wanted one of their records returned.
    (It doesn't matter which one, I only want one of them)

Posting Permissions

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