/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

0 vote226 views1 answer
Placements hackerrank solution
SQL
0 vote233 views1 answer
How to find columns with null values in sql
SQL
0 vote228 views1 answer
Neo4j command to run script file
SQL
0 vote221 views1 answer
Mysql foreach insert
SQL
0 vote209 views1 answer
Sql server update top n records
SQL
0 vote220 views2 answers
Sql update multiple tables
SQL
0 vote220 views1 answer
Frename oracle
SQL
-1 vote212 views1 answer
Convert google analytics dathourminute to time stamp? sql
SQL
2 votes210 views1 answer
How to select first row of database sql
SQL
1 vote217 views1 answer
Oracle datafile max size 32gb
SQL