SQL - Remove the Duplicate Results

SQL - Remove the duplicate Results

Use DISTINCT:

SELECT DISTINCT name, surname
FROM yourtable
WHERE name LIKE '%j%' OR surname LIKE '%j%'

How can I remove duplicate rows?

Assuming no nulls, you GROUP BY the unique columns, and SELECT the MIN (or MAX) RowId as the row to keep. Then, just delete everything that didn't have a row id:

DELETE FROM MyTable
LEFT OUTER JOIN (
   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 
   FROM MyTable 
   GROUP BY Col1, Col2, Col3
) as KeepRows ON
   MyTable.RowId = KeepRows.RowId
WHERE
   KeepRows.RowId IS NULL

In case you have a GUID instead of an integer, you can replace

MIN(RowId)

with

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))

How to delete duplicate records in SQL?

You can delete duplicates using i.e. ROW_NUMBER():

with duplicates as
(
    select
        *
        ,ROW_NUMBER() OVER (PARTITION BY FirstName, LastName, age ORDER BY FirstName) AS number
    from yourTable
)
delete 
from duplicates
where number > 1

Each row where number is bigger than 1 is a duplicate.

How to remove duplicates based on a certain column in SQL Server?

We can use a deletable CTE along with ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY fid ORDER BY date DESC, name) rn
    FROM yourTable
)

DELETE
FROM cte
WHERE rn > 1;

The above logic will assign rn = 1 (i.e. spare) the record with the most recent date, per group of fid records. Should two records with the same fid also have the same latest date, then it spares the earlier name.

Remove duplicate rows from mysql table result

If you expect your query to return the number of duplicates then no it is not correct.

The condition t1.id < t2.id will join every id of t1 with all ids from t2 that are greater resulting on more rows or less rows (in the case of only 2 duplicates) and rarely in the expected number.

See the demo.

If you want to see all the duplicates:

select * from consignment t
where t.service = 'CLRC' 
and exists (
  select 1 from consignment
  where service = t.service and id <> t.id and hawb = t.hawb 
)  

See the demo.

If you want to delete the duplicates and keep only the one ones with the max id for each hawb then:

delete from consignment
where service='CLRC'
and id not in (
  select id from (
    select max(id) id from consignment
    where service='CLRC' 
    group by hawb
  ) t  
);

See the demo.

How to remove duplicate MySQL records (but only leave one)

You can use this to keep the row with the lowest id value

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id > e2.id AND e1.email = e2.email;

this an example link link 1

or you can change > to < for keep the highest id

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id < e2.id AND e1.email = e2.email;

this an example link link 2

Remove old duplicate rows in BQ based on timestamp

You can try this script. Used COUNT() with HAVING to pull duplicate records with timestamp older than 120 minutes from current time using TIMESTAMP_DIFF.

DELETE
FROM `table_full_name` 
WHERE ad_id in (SELECT ad_id 
                    FROM `table_full_name`
                    GROUP BY ad_id
                    HAVING COUNT(ad_id) > 1) 
AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), timestamp, MINUTE) > 120

Before:

After:

Removing duplicate rows (based on values from multiple columns) from SQL table

Sample SQL FIDDLE

1) Use CTE to get max ship code value record based on ARDivisionNo, CustomerNo
for each Customers

WITH cte AS (
  SELECT*, 
     row_number() OVER(PARTITION BY ARDivisionNo, CustomerNo ORDER BY ShipToCode desc) AS [rn]
  FROM t
)
Select * from cte WHERE [rn] = 1

2) To Delete the record use Delete query instead of Select and change Where Clause to rn > 1. Sample SQL FIDDLE

WITH cte AS (
  SELECT*, 
     row_number() OVER(PARTITION BY ARDivisionNo, CustomerNo ORDER BY ShipToCode desc) AS [rn]
  FROM t
)
Delete from cte WHERE [rn] > 1;

select * from t;

---

Related Topics