Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Selecting Unique Records

Discussion in 'Information Technology' started by Always Learning, Nov 11, 2005.

  1. Always Learning

    Always Learning
    Expand Collapse
    Guest

    Hi Guys,

    Could you help me please?

    I have a table with three columns.
    1. RefNum
    2. Salutation
    3.Email Address

    I want to pull out of the single table all the records with a unique email
    address. so if there are 4 records with the same email I want just 1 of the
    4 records with that email address and the others to be deleted.
    If there is only 1 occurance of an email address pull that out also.


    Thanks for your help, I appreciate it.

    Best Regards,

    Steve.
     
  2. Loading...

    Similar Threads Forum Date
    India Gwalior: A Unique Link to India's History Breaking News Nov 16, 2013
    Heritage Sikh Art: Reflections Of A Unique Spiritual, Secular Sikhism Identity History of Sikhism Jan 9, 2013
    General Great Sindhi Folk, wonderfully unique music style: 1 Videos Feb 23, 2012
    Mother Hope: Prakash Kaur and her Unique Home for Girls Inspirational Stories Feb 1, 2011
    In US, Sikhs succeed in push for understanding their unique culture Interfaith Dialogues Jun 18, 2010

  3. Bill Edwards

    Bill Edwards
    Expand Collapse
    Guest

    The following will pull a single record for each distinct email address. In
    the case of duplicates it will keep the record with the highest value in
    RefNum:

    SELECT tblTest.Email, tblTest.RefNum, tblTest.Salutation
    FROM tblTest
    WHERE
    tblTest.RefNum IN (SELECT max( refnum) from tblTest as b GROUP BY email)

    "Always Learning" <NoMoreSpam@MyEmail> wrote in message
    news:%23OtvUyg5FHA.884@TK2MSFTNGP14.phx.gbl...
    > Hi Guys,
    >
    > Could you help me please?
    >
    > I have a table with three columns.
    > 1. RefNum
    > 2. Salutation
    > 3.Email Address
    >
    > I want to pull out of the single table all the records with a unique email
    > address. so if there are 4 records with the same email I want just 1 of
    > the
    > 4 records with that email address and the others to be deleted.
    > If there is only 1 occurance of an email address pull that out also.
    >
    >
    > Thanks for your help, I appreciate it.
    >
    > Best Regards,
    >
    > Steve.
    >
    >
     
  4. Always Learning

    Always Learning
    Expand Collapse
    Guest

    Hi Bill,

    That looks perfect, I will try it later.

    Thanks for your time, I appreciate it.

    Best Regards,

    Steve Wilson.

    "Bill Edwards" <billedwards@msn.com> wrote in message
    news:u%23wxV7h5FHA.3908@tk2msftngp13.phx.gbl...
    > The following will pull a single record for each distinct email address.

    In
    > the case of duplicates it will keep the record with the highest value in
    > RefNum:
    >
    > SELECT tblTest.Email, tblTest.RefNum, tblTest.Salutation
    > FROM tblTest
    > WHERE
    > tblTest.RefNum IN (SELECT max( refnum) from tblTest as b GROUP BY email)
    >
    > "Always Learning" <NoMoreSpam@MyEmail> wrote in message
    > news:%23OtvUyg5FHA.884@TK2MSFTNGP14.phx.gbl...
    > > Hi Guys,
    > >
    > > Could you help me please?
    > >
    > > I have a table with three columns.
    > > 1. RefNum
    > > 2. Salutation
    > > 3.Email Address
    > >
    > > I want to pull out of the single table all the records with a unique

    email
    > > address. so if there are 4 records with the same email I want just 1 of
    > > the
    > > 4 records with that email address and the others to be deleted.
    > > If there is only 1 occurance of an email address pull that out also.
    > >
    > >
    > > Thanks for your help, I appreciate it.
    > >
    > > Best Regards,
    > >
    > > Steve.
    > >
    > >

    >
    >
     
  5. Always Learning

    Always Learning
    Expand Collapse
    Guest

    Hi Bill,

    Tried that and it works a treat, thankyou.

    Sorry to put on you again but is there a way to put the records it finds as
    duplicates into a seperate table.

    Thanks for your help.

    Best Regards,

    Steve Wilson.

    "Bill Edwards" <billedwards@msn.com> wrote in message
    news:u%23wxV7h5FHA.3908@tk2msftngp13.phx.gbl...
    > The following will pull a single record for each distinct email address.

    In
    > the case of duplicates it will keep the record with the highest value in
    > RefNum:
    >
    > SELECT tblTest.Email, tblTest.RefNum, tblTest.Salutation
    > FROM tblTest
    > WHERE
    > tblTest.RefNum IN (SELECT max( refnum) from tblTest as b GROUP BY email)
    >
    > "Always Learning" <NoMoreSpam@MyEmail> wrote in message
    > news:%23OtvUyg5FHA.884@TK2MSFTNGP14.phx.gbl...
    > > Hi Guys,
    > >
    > > Could you help me please?
    > >
    > > I have a table with three columns.
    > > 1. RefNum
    > > 2. Salutation
    > > 3.Email Address
    > >
    > > I want to pull out of the single table all the records with a unique

    email
    > > address. so if there are 4 records with the same email I want just 1 of
    > > the
    > > 4 records with that email address and the others to be deleted.
    > > If there is only 1 occurance of an email address pull that out also.
    > >
    > >
    > > Thanks for your help, I appreciate it.
    > >
    > > Best Regards,
    > >
    > > Steve.
    > >
    > >

    >
    >
     
  6. Bill Edwards

    Bill Edwards
    Expand Collapse
    Guest

    Create new table based on the duplicate records:

    SELECT [tblTest]., [tblTest].[RefNum], [tblTest].[Salutation] INTO
    tblDuplicateValues
    FROM tblTest
    WHERE (((tblTest.RefNum) Not In (SELECT max( refnum) from tblTest as b GROUP
    BY email)));

    Create new table based on just single records:

    SELECT [tblTest].[Email], [tblTest].[RefNum], [tblTest].[Salutation] INTO
    tblUniqueValues
    FROM tblTest
    WHERE (((tblTest.RefNum) IN (SELECT max( refnum) from tblTest as b GROUP BY
    email)));


    "Always Learning" <NoMoreSpam@MyEmail> wrote in message
    news:uB%23H6qq5FHA.2776@TK2MSFTNGP15.phx.gbl...[color=blue]
    > Hi Bill,
    >
    > Tried that and it works a treat, thankyou.
    >
    > Sorry to put on you again but is there a way to put the records it finds
    > as
    > duplicates into a seperate table.
    >
    > Thanks for your help.
    >
    > Best Regards,
    >
    > Steve Wilson.
    >
    > "Bill Edwards" <billedwards@msn.com> wrote in message
    > news:u%23wxV7h5FHA.3908@tk2msftngp13.phx.gbl...[color=green]
    >> The following will pull a single record for each distinct email address.[/color]
    > In[color=green]
    >> the case of duplicates it will keep the record with the highest value in
    >> RefNum:
    >>
    >> SELECT tblTest.Email, tblTest.RefNum, tblTest.Salutation
    >> FROM tblTest
    >> WHERE
    >> tblTest.RefNum IN (SELECT max( refnum) from tblTest as b GROUP BY email)
    >>
    >> "Always Learning" <NoMoreSpam@MyEmail> wrote in message
    >> news:%23OtvUyg5FHA.884@TK2MSFTNGP14.phx.gbl...[color=darkred]
    >> > Hi Guys,
    >> >
    >> > Could you help me please?
    >> >
    >> > I have a table with three columns.
    >> > 1. RefNum
    >> > 2. Salutation
    >> > 3.Email Address
    >> >
    >> > I want to pull out of the single table all the records with a unique[/color][/color]
    > email[color=green][color=darkred]
    >> > address. so if there are 4 records with the same email I want just 1 of
    >> > the
    >> > 4 records with that email address and the others to be deleted.
    >> > If there is only 1 occurance of an email address pull that out also.
    >> >
    >> >
    >> > Thanks for your help, I appreciate it.
    >> >
    >> > Best Regards,
    >> >
    >> > Steve.
    >> >
    >> >[/color]
    >>
    >>[/color]
    >
    >[/color]
     
  7. Always Learning

    Always Learning
    Expand Collapse
    Guest

    Hi Bill,

    Thanks again. That worked perfect.
    You certainly know your stuff.

    Best Regards,

    Steve Wilson.

    "Bill Edwards" <billedwards@msn.com> wrote in message
    news:eT4Xnxs5FHA.4036@TK2MSFTNGP11.phx.gbl...
    > Create new table based on the duplicate records:
    >
    > SELECT [tblTest]., [tblTest].[RefNum], [tblTest].[Salutation] INTO
    > tblDuplicateValues
    > FROM tblTest
    > WHERE (((tblTest.RefNum) Not In (SELECT max( refnum) from tblTest as b[/color]
    GROUP[color=blue]
    > BY email)));
    >
    > Create new table based on just single records:
    >
    > SELECT [tblTest].[Email], [tblTest].[RefNum], [tblTest].[Salutation] INTO
    > tblUniqueValues
    > FROM tblTest
    > WHERE (((tblTest.RefNum) IN (SELECT max( refnum) from tblTest as b GROUP[/color]
    BY[color=blue]
    > email)));
    >
    >
    > "Always Learning" <NoMoreSpam@MyEmail> wrote in message
    > news:uB%23H6qq5FHA.2776@TK2MSFTNGP15.phx.gbl...[color=green]
    > > Hi Bill,
    > >
    > > Tried that and it works a treat, thankyou.
    > >
    > > Sorry to put on you again but is there a way to put the records it finds
    > > as
    > > duplicates into a seperate table.
    > >
    > > Thanks for your help.
    > >
    > > Best Regards,
    > >
    > > Steve Wilson.
    > >
    > > "Bill Edwards" <billedwards@msn.com> wrote in message
    > > news:u%23wxV7h5FHA.3908@tk2msftngp13.phx.gbl...[color=darkred]
    > >> The following will pull a single record for each distinct email[/color][/color][/color]
    address.[color=blue][color=green]
    > > In[color=darkred]
    > >> the case of duplicates it will keep the record with the highest value[/color][/color][/color]
    in[color=blue][color=green][color=darkred]
    > >> RefNum:
    > >>
    > >> SELECT tblTest.Email, tblTest.RefNum, tblTest.Salutation
    > >> FROM tblTest
    > >> WHERE
    > >> tblTest.RefNum IN (SELECT max( refnum) from tblTest as b GROUP BY[/color][/color][/color]
    email)[color=blue][color=green][color=darkred]
    > >>
    > >> "Always Learning" <NoMoreSpam@MyEmail> wrote in message
    > >> news:%23OtvUyg5FHA.884@TK2MSFTNGP14.phx.gbl...
    > >> > Hi Guys,
    > >> >
    > >> > Could you help me please?
    > >> >
    > >> > I have a table with three columns.
    > >> > 1. RefNum
    > >> > 2. Salutation
    > >> > 3.Email Address
    > >> >
    > >> > I want to pull out of the single table all the records with a unique[/color]
    > > email[color=darkred]
    > >> > address. so if there are 4 records with the same email I want just 1[/color][/color][/color]
    of[color=blue][color=green][color=darkred]
    > >> > the
    > >> > 4 records with that email address and the others to be deleted.
    > >> > If there is only 1 occurance of an email address pull that out also.
    > >> >
    > >> >
    > >> > Thanks for your help, I appreciate it.
    > >> >
    > >> > Best Regards,
    > >> >
    > >> > Steve.
    > >> >
    > >> >
    > >>
    > >>[/color]
    > >
    > >[/color]
    >
    >[/color]
     

Share This Page