/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 vote206 views1 answer
Sql server change schema of a table
SQL
23 votes188 views6 answers
How to create table in sql
SQL
0 vote252 views1 answer
Sqlite3 pragma foreign keys
SQL
1 vote205 views1 answer
Psql view enum values
SQL
1 vote195 views1 answer
Backup mysql data only
SQL
0 vote206 views1 answer
Ajouter une clé etrangere mysql
SQL
0 vote197 views1 answer
Sql "List the contact methods found in the Contact table.For each contact method, list how many people chose that contact method."
SQL
1 vote240 views1 answer
Psql client write to bash variable
SQL
0 vote190 views1 answer
Run mssql server terminal
SQL
0 vote200 views1 answer
Ansi sql count duplicate rows
SQL