/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 vote106 views2 answers
Getting customers with no orders sql
SQL
0 vote112 views1 answer
Mysql find the row ites of the hoghest value at on column
SQL
0 vote95 views1 answer
Close sql query vb.net
SQL
1 vote100 views2 answers
Oracle replace
SQL
4 votes114 views1 answer
Describe table mysql
SQL
6 votes109 views2 answers
Mysql interval 1 day
SQL
2 votes103 views4 answers
Check timezone of mysql database
SQL
0 vote109 views1 answer
Find wordpress version in database
SQL
4 votes60 views2 answers
Select todays data postgres query
SQL
2 votes99 views1 answer
Oracle find unusable indexes
SQL