March 9, 2013 at 3:37 am
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?
March 9, 2013 at 7:59 am
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
March 9, 2013 at 8:14 am
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
March 9, 2013 at 8:16 am
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
March 9, 2013 at 11:58 pm
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
March 10, 2013 at 10:20 am
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
March 10, 2013 at 12:22 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply