/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 vote286 views1 answer
Sql contains vs like
SQL
0 vote296 views1 answer
Ms sql filter all sympbol
SQL
7 votes333 views3 answers
Sql update where id
SQL
0 vote293 views1 answer
Normalization in database 2nd normal form
SQL
0 vote315 views1 answer
Insert using condition postgres
SQL
50 votes357 views6 answers
Install mysql on ubuntu
SQL
5 votes277 views1 answer
Mysql get last day of month
SQL
-1 vote281 views3 answers
Create temp table in sql
SQL
3 votes288 views2 answers
Monthname sql
SQL
1 vote286 views1 answer
Sql run multiple updates in one query
SQL