/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

25 votes123 views6 answers
Import database mysql command line
SQL
5 votes107 views6 answers
Sql add days to date
SQL
1 vote119 views1 answer
Blob datatype in mysql
SQL
-3 votes110 views2 answers
How to enable mysql authentication on ubuntu 18.04
SQL
0 vote113 views1 answer
Sql server delete records with specific date
SQL
1 vote124 views1 answer
UseSqlServer no definition
SQL
9 votes128 views4 answers
Last mysql
SQL
9 votes107 views4 answers
Mysql show table fields
SQL
0 vote98 views1 answer
Select STR_TO_DATE(date_seance1,'DD-MM-YYYY')
SQL
14 votes116 views4 answers
Import database through command line
SQL