show only one records for duplicate

Question

prit005 0 Light Poster

13 Years Ago

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!

mssql

7 Contributors
11 Replies
231 Views
2 Weeks Discussion Span
Latest Post 13 Years Ago Latest Post by Netcode

debasisdas 580 Posting Genius

13 Years Ago

Use distinct.

BitBlt 452 Practically a Master Poster

13 Years Ago

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

BitBlt 452 Practically a Master Poster

13 Years Ago

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.

adam_k commented: Again, I have to agree +4

Reply to this topic

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.

adam_k 239 Master Poster · Answer 1 · 2011-07-13T03:29:34+00:00

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?

prit005 0 Light Poster · Answer 2 · 2011-07-13T17:29:42+00:00

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

prit005 0 Light Poster · Answer 3 · 2011-07-13T17:31:04+00:00

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

prit005 0 Light Poster · Answer 4 · 2011-07-13T17:32:11+00:00

Use distinct.

but distinct not work i tried it.

debasisdas 580 Posting Genius Featured Poster · Answer 5 · 2011-07-13T19:18:12+00:00

try this way

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

sknake 1,622 Senior Poster Featured Poster · Answer 6 · 2011-07-14T11:09:26+00:00

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

Tommymac501 4 Junior Poster in Training · Answer 7 · 2011-07-22T02:54:43+00:00

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.

Netcode 33 Veteran Poster · Answer 8 · 2011-07-26T13:41:08+00:00

Netcode 33 Veteran Poster

13 Years Ago

use the DISTINCT keyword