LEADID ACCOUNTMANAGERID ActivityDate USERNAME
Q6UJ9A08MZSN U6UJ9A0000IR Mar- 1-2011 KSHITIJ GUPTA
Q6UJ9A08MZSN U6UJ9A0000IR Mar- 1-2011 KSHITIJ GUPTA
ACCOUNTNAME
Kayess Finance & Investments
Kayess Finance & Investments

show only one records for duplicate
this o/p come from various join it just an sample data i have near about 250 duplicate records in to table in this table i want to fetch only single record for the duplicate entries.
means to only one record for each duplicate entry.

in this case i use distinct but it is not work!

Use distinct.

...in this case i use distinct but it is not work!

Please post your query. If distinct doesn't work then the rows aren't really duplicates.

BitBit is right, if distinct doesn't work then the rows aren't duplicates.

Do you have multiple records in some table or are these records coming from a cartesian join?

Please post your query. If distinct doesn't work then the rows aren't really duplicates.

Query============

select distinct l.LEADID,l.ACCOUNTMANAGERID ,
 MAX((SUBSTRING (convert(varchar, h.STARTDATE, 100),1,3)+'-'+
 SUBSTRING(CONVERT (VARCHAR, h.startdate,100),5,2)
 +'-'+SUBSTRING (convert(varchar, h.STARTDATE, 100),8,4 ) ))as ActivityDate,
 ROW_NUMBER() over(Partition by h.ContactName  order by l.ACCOUNTMANAGERID ) as MaxCount ,h.ACTIVITYID as Activities, 
--left(CAST(FLOOR( CAST( h.STARTDATE AS FLOAT ) ) AS DATETIME ), 11) as ActivityDate,
left(CAST(FLOOR( CAST(h.createDate AS FLOAT ) ) AS DATETIME ), 11) as CreateDate,
'Week' + '  ' + cast (DATEPART(DAY, h.STARTDATE ) / 7 + 1 as varchar) as Week ,
Datename(MONTH, h.STARTDATE )as Month,
u.USERNAME,
ACCOUNTNAME,
CONTACTNAME,
h.DESCRIPTION,
h.NOTES,
ad.CITY, 
ad.STATE,
l.siccode as Region,
h.USERID
from  LEAD l inner join history h on h.LEADID=l.LEADID inner join USERINFO u on h.USERID =u.USERID
left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
--USERINFO u inner join history h on h.userid = u.userid inner join LEAD l on h.leadid = l.leadid left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
where 
l.ACCOUNTMANAGERID=u.USERID and
--l.LEADID=h.LEADID and
--l.LEADID<>h.LEADID and
ACTIVITYID is not null
and h.CREATEDATE > = '2011-04-01 00:00:00.000'
and h.LEADID is not null
and h.ACCOUNTID is null
and l.LEADCATEGORY like 'mem%' and h.StartDate < getDate() 
group by ACCOUNTID , l.ACCOUNTMANAGERID , l.LEADID, h.CONTACTNAME,u.USERNAME,ACTIVITYID,h.STARTDATE,h.CREATEDATE,h.ACCOUNTNAME
,h.DESCRIPTION,h.NOTES,ad.CITY,ad.STATE,l.SICCODE,h.USERID 
having COUNT(distinct l.LEADID)=1--(h.CONTACTNAME)>=1

UNION

select distinct  l.LEADID,l.ACCOUNTMANAGERID ,
 Max((SUBSTRING (convert(varchar, a.STARTDATE, 100),1,3)+'/'+
 SUBSTRING(CONVERT (VARCHAR, a.startdate,100),5,2)
 +'/'+SUBSTRING (convert(varchar, a.STARTDATE, 100),8,4 ) ))as ActivityDate, 
 ROW_NUMBER() over(Partition by ContactName  order by l.ACCOUNTMANAGERID ) as MaxCount ,activityid as Activities,
--left(CAST(FLOOR( CAST( a.STARTDATE AS FLOAT ) ) AS DATETIME ), 11) as ActivityDate,
left(CAST(FLOOR( CAST( a.CreateDate AS FLOAT ) ) AS DATETIME ), 11) as CreateDate,
'Week' + '  ' + cast (DATEPART(DAY, a.STARTDATE ) / 7 + 1 as varchar)as Week ,
Datename(MONTH, a.STARTDATE ) as Month,
u.USERNAME,
ACCOUNTNAME,
CONTACTNAME,
a.DESCRIPTION,
a.NOTES,
ad.CITY, 
ad.STATE,
l.siccode as Region,
u.userid
from  
LEAD l inner join ACTIVITY a on a.LEADID=l.LEADID inner join USERINFO u on a.USERID =u.USERID
left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
--USERINFO u inner join ACTIVITY a on a.userid = u.userid inner join LEAD l on a.leadid = l.leadid left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
where a.userid = u.userid and
--l.LEADID=a.LEADID and
--l.LEADID<> a.LEADID and
l.ACCOUNTMANAGERID=u.USERID
and a.CREATEDATE > = '2011-04-01 00:00:00.000'
and a.LEADID is not null
and a.ACCOUNTID is null
and l.LEADCATEGORY like 'mem%' and a.StartDate < getDate()
group by ACCOUNTID , l.ACCOUNTMANAGERID ,l.LEADID, CONTACTNAME,u.USERNAME,ACTIVITYID,a.STARTDATE,u.USERID ,a.CREATEDATE,a.ACCOUNTNAME
,a.DESCRIPTION,a.NOTES,ad.CITY,ad.STATE,l.SICCODE,a.USERID having COUNT(distinct l.LEADID)=1   
--COUNT(ContactName)>=1

BitBit is right, if distinct doesn't work then the rows aren't duplicates.

Do you have multiple records in some table or are these records coming from a cartesian join?

Yes Records come from join I paste the query please ckeck it

select distinct l.LEADID,l.ACCOUNTMANAGERID ,
 MAX((SUBSTRING (convert(varchar, h.STARTDATE, 100),1,3)+'-'+
 SUBSTRING(CONVERT (VARCHAR, h.startdate,100),5,2)
 +'-'+SUBSTRING (convert(varchar, h.STARTDATE, 100),8,4 ) ))as ActivityDate,
 ROW_NUMBER() over(Partition by h.ContactName  order by l.ACCOUNTMANAGERID ) as MaxCount ,h.ACTIVITYID as Activities, 
--left(CAST(FLOOR( CAST( h.STARTDATE AS FLOAT ) ) AS DATETIME ), 11) as ActivityDate,
left(CAST(FLOOR( CAST(h.createDate AS FLOAT ) ) AS DATETIME ), 11) as CreateDate,
'Week' + '  ' + cast (DATEPART(DAY, h.STARTDATE ) / 7 + 1 as varchar) as Week ,
Datename(MONTH, h.STARTDATE )as Month,
u.USERNAME,
ACCOUNTNAME,
CONTACTNAME,
h.DESCRIPTION,
h.NOTES,
ad.CITY, 
ad.STATE,
l.siccode as Region,
h.USERID
from  LEAD l inner join history h on h.LEADID=l.LEADID inner join USERINFO u on h.USERID =u.USERID
left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
--USERINFO u inner join history h on h.userid = u.userid inner join LEAD l on h.leadid = l.leadid left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
where 
l.ACCOUNTMANAGERID=u.USERID and
--l.LEADID=h.LEADID and
--l.LEADID<>h.LEADID and
ACTIVITYID is not null
and h.CREATEDATE > = '2011-04-01 00:00:00.000'
and h.LEADID is not null
and h.ACCOUNTID is null
and l.LEADCATEGORY like 'mem%' and h.StartDate < getDate() 
group by ACCOUNTID , l.ACCOUNTMANAGERID , l.LEADID, h.CONTACTNAME,u.USERNAME,ACTIVITYID,h.STARTDATE,h.CREATEDATE,h.ACCOUNTNAME
,h.DESCRIPTION,h.NOTES,ad.CITY,ad.STATE,l.SICCODE,h.USERID 
having COUNT(distinct l.LEADID)=1--(h.CONTACTNAME)>=1

UNION

select distinct  l.LEADID,l.ACCOUNTMANAGERID ,
 Max((SUBSTRING (convert(varchar, a.STARTDATE, 100),1,3)+'/'+
 SUBSTRING(CONVERT (VARCHAR, a.startdate,100),5,2)
 +'/'+SUBSTRING (convert(varchar, a.STARTDATE, 100),8,4 ) ))as ActivityDate, 
 ROW_NUMBER() over(Partition by ContactName  order by l.ACCOUNTMANAGERID ) as MaxCount ,activityid as Activities,
--left(CAST(FLOOR( CAST( a.STARTDATE AS FLOAT ) ) AS DATETIME ), 11) as ActivityDate,
left(CAST(FLOOR( CAST( a.CreateDate AS FLOAT ) ) AS DATETIME ), 11) as CreateDate,
'Week' + '  ' + cast (DATEPART(DAY, a.STARTDATE ) / 7 + 1 as varchar)as Week ,
Datename(MONTH, a.STARTDATE ) as Month,
u.USERNAME,
ACCOUNTNAME,
CONTACTNAME,
a.DESCRIPTION,
a.NOTES,
ad.CITY, 
ad.STATE,
l.siccode as Region,
u.userid
from  
LEAD l inner join ACTIVITY a on a.LEADID=l.LEADID inner join USERINFO u on a.USERID =u.USERID
left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
--USERINFO u inner join ACTIVITY a on a.userid = u.userid inner join LEAD l on a.leadid = l.leadid left outer join ADDRESS ad  on l.LEAD_ADDRESSID = ad.ADDRESSID
where a.userid = u.userid and
--l.LEADID=a.LEADID and
--l.LEADID<> a.LEADID and
l.ACCOUNTMANAGERID=u.USERID
and a.CREATEDATE > = '2011-04-01 00:00:00.000'
and a.LEADID is not null
and a.ACCOUNTID is null
and l.LEADCATEGORY like 'mem%' and a.StartDate < getDate()
group by ACCOUNTID , l.ACCOUNTMANAGERID ,l.LEADID, CONTACTNAME,u.USERNAME,ACTIVITYID,a.STARTDATE,u.USERID ,a.CREATEDATE,a.ACCOUNTNAME
,a.DESCRIPTION,a.NOTES,ad.CITY,ad.STATE,l.SICCODE,a.USERID having COUNT(distinct l.LEADID)=1   
--COUNT(ContactName)>=

1

Use distinct.

but distinct not work i tried it.

try this way

select distinct f1,f2,f3 from
(select distinct f1,f2,f3 from table1
union
select distinct f1,f2,f3 from table2
)

My guess (and it is just a guess, since I can't see your data) is that Row_Number() being based on the History table is what's killing the "distinct" clause. That's where I would investigate first.

commented: Again, I have to agree +4

Group by every column you select. If the duplicates are somehow coming from a join it will still be eliminated in a group by.

Example:

IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
GO
Create Table #Table
(
  Id int identity(1000, 1) PRIMARY KEY,
  Name varchar(100)
)

SET NOCOUNT ON
Insert Into #Table (Name) Values ('sknake')
Insert Into #Table (Name) Values ('adatapost')
Insert Into #Table (Name) Values ('ddanbe')
Insert Into #Table (Name) Values ('ramy')
SET NOCOUNT OFF

Select *
From
(
  Select * From #Table
  Union All
  Select * From #Table
  Union All
  Select * From #Table
) as tbl
Group By Id, Name --Try running this query with and without the group by

Won't doing a master select with sub selects unioned (not UNION ALL) eliminate dupes in the resulting data set? I thought that was the difference between UNION and UNION ALL, UNION Does not allow dupes between tables, and UNION ALL does.

use the DISTINCT keyword

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.