Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Eliminate duplicate in the same column Expand / Collapse
Author
Message
Posted Saturday, March 9, 2013 3:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
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
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

I have to eliminate the duplicate party name. Can anybody help me?
Post #1428863
Posted Saturday, March 9, 2013 7:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
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
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

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1428891
Posted Saturday, March 9, 2013 8:14 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 21,631, Visits: 15,290
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1428893
Posted Saturday, March 9, 2013 8:16 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 21,631, Visits: 15,290
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1428894
Posted Saturday, March 9, 2013 11:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 1, 2013 12:41 AM
Points: 44, Visits: 84
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
Post #1428958
Posted Sunday, March 10, 2013 10:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1428992
Posted Sunday, March 10, 2013 12:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 23,243, Visits: 31,938
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1429002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse