InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 08:22:16 GMT20http://www.sqlservercentral.com/Forums/Topic1428863-1291-1.aspxAs this is posted in a SQL Server 2005 Forum, the following will work as well:[code="sql"]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 #TestTablevalues('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, PARTYNAMEfrom BaseDatawhere RN = 1order by COMCOD, PARTYCODE;if object_id('tempdb..#TestTable') is not null drop table #TestTable;GO[/code]Sun, 10 Mar 2013 12:22:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1428863-1291-1.aspxThen 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.[code="sql"] SELECT ComCod = MIN(COMCOD), PartyCode = MIN(PartyCode), PartyName FROM dbo.YourTable GROUP BY PartyName ORDER BY PartyName;[/code]Sun, 10 Mar 2013 10:20:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1428863-1291-1.aspxI like to see the query as below where no duplicate party name.COMCOD PARTY CODE PARTYNAME3305 100028 Premier Cement Mills Ltd.3305 100033 Electro Power Engineering Ltd.3305 100037 Islam Trading Consortium Ltd.3305 100040 Mr. Ehsanur RahmanSat, 09 Mar 2013 23:58:37 GMTRauf Miahhttp://www.sqlservercentral.com/Forums/Topic1428863-1291-1.aspxThen 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?Sat, 09 Mar 2013 08:16:44 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1428863-1291-1.aspxHere's an article on how to do that[url]http://jasonbrimhall.info/2011/03/21/dedupe-data-cte/[/url]Sat, 09 Mar 2013 08:14:02 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1428863-1291-1.aspx[quote][b]zahid_7777 (3/9/2013)[/b][hr]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 Rahman3305 100041 Mr. Ehsanur RahmanI have to eliminate the duplicate party name. Can anybody help me?[/quote]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.Sat, 09 Mar 2013 07:59:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1428863-1291-1.aspxI 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 Rahman3305 100041 Mr. Ehsanur RahmanI have to eliminate the duplicate party name. Can anybody help me?Sat, 09 Mar 2013 03:37:29 GMTRauf Miah