Results 1 to 5 of 5

Thread: removing duplicates

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2017
    Posts
    105

    removing duplicates

    in access i have this table like so:

    MyTable:
    ID,Autonumber
    Name,Text

    ID Name
    1 A
    2 B
    3 C
    4 D
    5 E
    6 F
    7 G
    8 H
    9 I
    10 J

    I have this self join like so:
    Code:
    SELECT T1.ID, T2.ID
    FROM MyTable AS T1, MyTable AS T2
    WHERE T1.ID<>T2.ID;
    result:
    T1.ID T2.ID
    2 1
    3 1
    4 1
    5 1
    6 1
    7 1
    8 1
    9 1
    10 1
    1 2
    and so on ....

    the problem is this result has duplicate combinations of T1.ID and T2.ID like so:
    T1.ID T2.ID
    2 1
    1 2

    3 1
    1 3
    and so on ....

    i can remove these duplicates by looping trough the result and removing the duplicates
    but i wonder if there is not a method to have the query not return these duplicates ?

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: removing duplicates

    An easy way is to simply remove a character (either the > or the < ), eg:
    Code:
    WHERE T1.ID < T2.ID;

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2017
    Posts
    105

    Re: removing duplicates

    wow si, fantastic thank you

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Feb 2017
    Posts
    105

    Re: removing duplicates

    si, i can slap myself in the face, your solution is so totally logical, i schould have thougt of it myself (old age i suppose)

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: removing duplicates

    There's no need for a slap... this is one of those situations where most people naturally get a blind-spot, I just have a slightly different view of the world which makes this kind of thing easier for me.

Posting Permissions

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



Click Here to Expand Forum to Full Width