/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

14 votes215 views3 answers
Oracle sql limit results
SQL
-1 vote187 views2 answers
On update cascade
SQL
-1 vote209 views2 answers
Import data from excel to sql server automatically
SQL
0 vote222 views3 answers
Call to undefined function mysql_connect()
SQL
1 vote202 views1 answer
Mysql check string don't have number
SQL
0 vote194 views2 answers
Postgres another version
SQL
3 votes206 views2 answers
Savepoint in sql
SQL
0 vote196 views2 answers
Oracle create tablespace autoextend
SQL
0 vote194 views2 answers
Php convert dbf to mysql
SQL
0 vote158 views1 answer
Mysql timeout 30 seconds
SQL