Eliminate duplicate in the same column

Question

Eliminate duplicate in the same column

Neel 7777

Right there with Babe

Points: 778
More actions
March 9, 2013 at 3:37 am

#275093

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?

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

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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

Neel 7777 Right there with Babe Points: 778 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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