Eliminate duplicate in the same column

  • I have a table which contains the field COMCOD data type nchar(4), PARTYCODE data type nchar(6) Primary Key, PARTYNAME nvarchar(250)

    COMCOD PARTY CODE PARTYNAME

    3305100028Premier Cement Mills Ltd.

    3305100029Premier Cement Mills Ltd.

    3305100030Premier Cement Mills Ltd.

    3305100031Premier Cement Mills Ltd.

    3305100032Premier Cement Mills Ltd.

    3305100033Electro Power Engineering Ltd.

    3305100034Electro Power Engineering Ltd.

    3305100035Electro Power Engineering Ltd.

    3305100036Electro Power Engineering Ltd.

    3305100037Islam Trading Consortium Ltd.

    3305100038Islam Trading Consortium Ltd.

    3305100039Islam Trading Consortium Ltd.

    3305100040Mr. Ehsanur Rahman

    3305100041Mr. Ehsanur Rahman

    I have to eliminate the duplicate party name. Can anybody help me?

  • zahid_7777 (3/9/2013)


    I have a table which contains the field COMCOD data type nchar(4), PARTYCODE data type nchar(6) Primary Key, PARTYNAME nvarchar(250)

    COMCOD PARTY CODE PARTYNAME

    3305100028Premier Cement Mills Ltd.

    3305100029Premier Cement Mills Ltd.

    3305100030Premier Cement Mills Ltd.

    3305100031Premier Cement Mills Ltd.

    3305100032Premier Cement Mills Ltd.

    3305100033Electro Power Engineering Ltd.

    3305100034Electro Power Engineering Ltd.

    3305100035Electro Power Engineering Ltd.

    3305100036Electro Power Engineering Ltd.

    3305100037Islam Trading Consortium Ltd.

    3305100038Islam Trading Consortium Ltd.

    3305100039Islam Trading Consortium Ltd.

    3305100040Mr. Ehsanur Rahman

    3305100041Mr. Ehsanur Rahman

    I have to eliminate the duplicate party name. Can anybody help me?

    There are several different outcomes to "eliminate the duplicate party name". Please post the result set that you'd expect using the data you provided above so we can figure it out.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Here's an article on how to do that

    http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Then again, maybe a little more description on what you seek.

    Are you just trying to eliminate dupes in your output, or eliminate dupes from the table?

    If you are trying to delete dupes, how do you know they are dupes? PartyCode is unique in each of those cases, what is the significance of that column?

    Are these the results from a table, or are they from a view that selects from multiple tables?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I like to see the query as below where no duplicate party name.

    COMCOD PARTY CODE PARTYNAME

    3305 100028 Premier Cement Mills Ltd.

    3305 100033 Electro Power Engineering Ltd.

    3305 100037 Islam Trading Consortium Ltd.

    3305 100040 Mr. Ehsanur Rahman

  • Then the following should do it for you. Please understand that I've not actually tested the code. Please see the first link in my signature line below for why and how to get really good answers to your posts in the future.

    SELECT ComCod = MIN(COMCOD)

    , PartyCode = MIN(PartyCode)

    , PartyName

    FROM dbo.YourTable

    GROUP BY PartyName

    ORDER BY PartyName

    ;

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • As this is posted in a SQL Server 2005 Forum, the following will work as well:

    if object_id('tempdb..#TestTable') is not null

    drop table #TestTable;

    create table #TestTable (

    COMCOD nchar(4),

    PARTYCODE nchar(6) PRIMARY KEY,

    PARTYNAME nvarchar(250));

    insert into #TestTable

    values

    ('3305','100028','Premier Cement Mills Ltd.'),

    ('3305','100029','Premier Cement Mills Ltd.'),

    ('3305','100030','Premier Cement Mills Ltd.'),

    ('3305','100031','Premier Cement Mills Ltd.'),

    ('3305','100032','Premier Cement Mills Ltd.'),

    ('3305','100033','Electro Power Engineering Ltd.'),

    ('3305','100034','Electro Power Engineering Ltd.'),

    ('3305','100035','Electro Power Engineering Ltd.'),

    ('3305','100036','Electro Power Engineering Ltd.'),

    ('3305','100037','Islam Trading Consortium Ltd.'),

    ('3305','100038','Islam Trading Consortium Ltd.'),

    ('3305','100039','Islam Trading Consortium Ltd.'),

    ('3305','100040','Mr. Ehsanur Rahman'),

    ('3305','100041','Mr. Ehsanur Rahman');

    with BaseData as (

    select

    COMCOD,

    PARTYCODE,

    PARTYNAME,

    RN = ROW_NUMBER() over (partition by COMCOD, PARTYNAME order by PARTYCODE asc)

    from

    #TestTAble

    )

    select

    COMCOD,

    PARTYCODE,

    PARTYNAME

    from

    BaseData

    where

    RN = 1

    order by

    COMCOD,

    PARTYCODE;

    if object_id('tempdb..#TestTable') is not null

    drop table #TestTable;

    GO

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply