/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

1 vote127 views1 answer
Tsql get beginning of year
SQL
1 vote118 views1 answer
Mysqli connect
SQL
0 vote131 views2 answers
How to create a table from another table in sql derby
SQL
0 vote123 views1 answer
Sql count * vs count 1
SQL
-2 votes131 views1 answer
Modify column name in tsql
SQL
0 vote126 views2 answers
Auto increment column in mysql query results
SQL
1 vote128 views1 answer
Add many column to sap iq table
SQL
1 vote105 views1 answer
Mysql see column types
SQL
0 vote121 views1 answer
Mysql where in keep order
SQL
6 votes136 views3 answers
Mysql remove user privileges
SQL