Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    24

    Unanswered: selecting only records with unique key

    This is more of an SQL problem then Access, but the database I am working with is an Access database.

    Anyways, here is the situation:

    Key Value1
    1 a
    1 ab
    1 abc
    2 abcd
    2 abcde
    3 abcdef


    Now I know how to find all the unique values for the field "Key", but I need to get the value with it. if i try SELECT DISTINCTROW, i just get all of those rows back.

    it doesn't particularly matter which value1 i get with the key, i just need any copy of value1.

    here is a sample result:

    Key Value1
    1 a
    2 abcde
    3 abcdef

    ANybody know how to do this? Thnx.

  2. #2
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117

    Cool

    Hey Fallacy,

    Here is one solution you can try:

    SELECT DISTINCT Key, FIRST(Value1) AS ValueField
    FROM Table1
    GROUP BY Key;

    Also, you can change the value FIRST to:

    FIRST
    LAST
    MAX
    MIN
    COUNT
    SUM
    AVG
    Etc........

    Let me know if this helps

    Kal

  3. #3
    Join Date
    May 2002
    Posts
    24
    yep, that works perfectly... dang, how come i didn't think of the FIRST function... heh.

Posting Permissions

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