Find duplicate records in a table using SQL Server

Find duplicate records in a table using SQL Server

I am validating a table which has a transaction level data of an eCommerce site and find the exact errors.

I want your help to find duplicate records in a 50 column table on SQL Server.

Suppose my data is:

OrderNo shoppername amountpayed city Item         1       Sam         10          A    Iphone  1       Sam         10          A    Iphone--->>Duplication to be detected  1       Sam         5           A    Ipod  2       John        20          B    Macbook  3       John        25          B    Macbookair  4       Jack        5           A    Ipod  

Suppose I use the below query:

Select shoppername,count(*) as cnt  from dbo.sales  having count(*) > 1  group by shoppername  

will return me

Sam  2  John 2  

But I don't want to find duplicate just over 1 or 2 columns. I want to find the duplicate over all the columns together in my data. I want the result as:

1       Sam         10          A    Iphone  

Answer by GolezTrol for Find duplicate records in a table using SQL Server

Just add all fields to the query and remember to add them to Group By as well.

Select shoppername, a, b, amountpayed, item, count(*) as cnt  from dbo.sales  group by shoppername, a, b, amountpayed, item  having count(*) > 1  

Answer by Eugene for Find duplicate records in a table using SQL Server

SELECT OrderNo, shoppername, amountPayed, city, item, count(*) as cnt  FROM dbo.sales  GROUP BY OrderNo, shoppername, amountPayed, city, item  HAVING COUNT(*) > 1  

Answer by Sathya Narayanan for Find duplicate records in a table using SQL Server

with x as   (select  *,rn = row_number()              over(PARTITION BY OrderNo,item  order by OrderNo)              from    #temp1)    select * from x  where rn > 1  

you can remove duplicates by replacing select statement by

delete x where rn > 1  

Answer by wqw for Find duplicate records in a table using SQL Server

Try this instead

SELECT MAX(shoppername), COUNT(*) AS cnt  FROM dbo.sales  GROUP BY CHECKSUM(*)  HAVING COUNT(*) > 1  

Read about the CHECKSUM function first, as there can be duplicates.

Answer by MUEKSH KUMAR for Find duplicate records in a table using SQL Server

SQL> SELECT JOB,COUNT(JOB) FROM EMP GROUP BY JOB;    JOB       COUNT(JOB)  --------- ----------  ANALYST            2  CLERK              4  MANAGER            3  PRESIDENT          1  SALESMAN           4  

Answer by Mahaveer for Find duplicate records in a table using SQL Server

Select * from dbo.sales group by shoppername having(count(Item) > 1)

Answer by Abhinav Singh for Find duplicate records in a table using SQL Server

To get the list of multiple records use following command

select field1,field2,field3, count(*)    from table_name    group by field1,field2,field3    having count(*) > 1  

Answer by user5758159 for Find duplicate records in a table using SQL Server

with x as (  select shoppername,count(shoppername)                from sales                having count(shoppername)>1              group by shoppername)  select t.* from x,win_gp_pin1510 t  where x.shoppername=t.shoppername  order by t.shoppername  

Answer by user5784803 for Find duplicate records in a table using SQL Server

Select EventID,count() as cnt from dbo.EventInstances group by EventID having count() > 1

Answer by Pushpendra Singh for Find duplicate records in a table using SQL Server

The following is running code:

SELECT abnno, COUNT(abnno)  FROM tbl_Name  GROUP BY abnno  HAVING ( COUNT(abnno) > 1 )  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72