/SQL/Sql remove duplicates based on column

Sql remove duplicates based on column

Query delete duplicates

DELETE FROM dups a USING (
      SELECT MIN(ctid) as ctid, key
        FROM dups 
        GROUP BY key HAVING COUNT(*) > 1
      ) b
      WHERE a.key = b.key 
      AND a.ctid <> b.ctid

Sql remove duplicates based on column

SELECT s.siteName, s.siteIP, h.date
FROM sites s INNER JOIN
     (select h.*, row_number() over (partition by siteName order by date desc) as seqnum
      from history h
     ) h
    ON s.siteName = h.siteName and seqnum = 1
ORDER BY s.siteName, h.date

Source: stackoverflow.com

Similar codes

8 votes246 views2 answers
Safe update mode in mysql
SQL
2 votes246 views2 answers
Microsoft sql server extract hour and minute from datetime
SQL
1 vote240 views2 answers
Mysql show views
SQL
0 vote253 views1 answer
Mssql unique key accept nulls
SQL
0 vote242 views1 answer
How to get first 10 records of a table in mysql
SQL
0 vote267 views2 answers
SQLSTATE[HY000]: General error: 3780 Referencing column 'role_id' and referenced column 'id' in foreign key constraint 'user_role_id foreign' are incompatible.
SQL
0 vote234 views1 answer
How to create a table in sql stack overflow
SQL
0 vote280 views1 answer
Sql values that contains certain multiple ids
SQL
2 votes260 views1 answer
Install postgresql centos 5
SQL
0 vote240 views1 answer
Azure sql server check foreign key
SQL