Member Avatar for prit005

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!

  • 7 Contributors
  • 11 Replies
  • 248 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by Netcode

Dani AI

Generated

Distinct not removing duplicates means the selected rows are not truly identical — small differences (different ACTIVITYID, milliseconds in STARTDATE, extra NULLs, or trailing spaces) will defeat DISTINCT. As and noted, first confirm which columns actually differ; 's suggestion to think in terms of grouping every output column points to the same diagnostic step.

A reliable pattern that avoids fragile DISTINCT/grouping across a multi-join query is to reduce the history/activity side to one row per logical key first, then join. One common approach is ROW_NUMBER() in a CTE (partition by the columns that define a "duplicate", order by the column used to choose the survivor), then select only rn = 1:

WITH Dedup AS (
  SELECT
    l.LEADID,
    l.ACCOUNTMANAGERID,
    h.CONTACTNAME,
    u.USERNAME,
    h.STARTDATE,
    h.ACTIVITYID,
    ROW_NUMBER() OVER (
      PARTITION BY l.LEADID, h.CONTACTNAME
      ORDER BY h.STARTDATE DESC, h.ACTIVITYID DESC
    ) AS rn
  FROM LEAD l
  JOIN history h ON h.LEADID = l.LEADID
  JOIN USERINFO u ON u.USERID = h.USERID
  WHERE h.CreateDate >= '2011-04-01'
)
SELECT * -- choose the columns required for the final output
FROM Dedup
WHERE rn = 1;

Alternatively, pre-aggregate the history table (MAX(StartDate) or MIN(ActivityID) per key) and join that result to the other tables; this prevents a many-to-many explosion from the join. If the query uses a UNION between two sources, de-duplicate after the UNION (or ensure both legs return the same projection) — UNION removes rows only when the entire selected row is identical, as implied.

Practical checks: compare LEN/TRIM of text columns, CAST or CONVERT datetime to the same precision (date-only if needed), or compute a hash (CHECKSUM/HASHBYTES) over the columns that define duplicates to spot hidden differences. These steps will identify the real duplicate key and make DISTINCT or GROUP BY actually work.

Recommended Answers

All 11 Replies

Member Avatar for debasisdas

Use distinct.

Member Avatar for BitBlt

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

Member Avatar for adam_k

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?

Edited by adam_k because: n/a
Member Avatar for prit005

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
Edited by Nick Evan because: Added CODE-tags
Member Avatar for prit005

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

Edited by Nick Evan because: Added CODE-tags
Member Avatar for prit005

Use distinct.

but distinct not work i tried it.

Member Avatar for debasisdas

try this way

select distinct f1,f2,f3 from
(select distinct f1,f2,f3 from table1
union
select distinct f1,f2,f3 from table2
)
Member Avatar for BitBlt

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
Member Avatar for sknake

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
Member Avatar for Tommymac501

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.

Member Avatar for Netcode

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.