/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

5 votes181 views1 answer
Commentaire table oracle
SQL
1 vote190 views3 answers
Update from select sqlite
SQL
4 votes178 views2 answers
Sqlalchemy_database_uri for mysql
SQL
0 vote174 views2 answers
Default constraint in ms sql
SQL
32 votes177 views6 answers
Insert select
SQL
17 votes204 views2 answers
Mysql date to string
SQL
1 vote196 views1 answer
Sql server kill all connections
SQL
0 vote172 views1 answer
Check psql validity function
SQL
8 votes180 views2 answers
Between operator
SQL
0 vote207 views1 answer
Datagrip go to line
SQL