/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 vote175 views3 answers
How to filter repeated same result using sql query
SQL
13 votes179 views2 answers
Mysql order by multiple columns
SQL
0 vote187 views1 answer
Odd record sql query
SQL
1 vote176 views1 answer
Index a database column sql
SQL
2 votes175 views1 answer
Oracle list grants on procedure
SQL
-1 vote183 views1 answer
Convert google analytics dathourminute to time stamp? sql
SQL
1 vote200 views3 answers
Psql: error: FATAL: role "postgres" does not exist
SQL
0 vote175 views1 answer
Mssql describe stored procedure sqlcmd
SQL
2 votes194 views2 answers
Mysql count rows returned
SQL
4 votes151 views1 answer
Install postgres 12 on ubuntu
SQL