/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 vote233 views1 answer
Get only one row in mysql
SQL
0 vote188 views1 answer
Vowels in sql
SQL
21 votes202 views3 answers
Search for column name in sql db when i don't know which table it is in
SQL
0 vote155 views1 answer
Select all domains of database firbird
SQL
0 vote226 views1 answer
T-SQL Never ending DELETE
SQL
0 vote202 views1 answer
Select place default lat long
SQL
0 vote211 views1 answer
Get last updated row sql server
SQL
-1 vote190 views2 answers
Postgres autoincrement primary key
SQL
0 vote201 views1 answer
Join in update query in mysql
SQL
0 vote198 views2 answers
Temp tables in sql server
SQL