/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

1 vote180 views1 answer
Get rows affected mysql python
SQL
0 vote198 views1 answer
Mysql change timestamp on update
SQL
4 votes138 views2 answers
Access to dialectresolutioninfo cannot be null when 'hibernate.dialect' not set
SQL
0 vote199 views1 answer
Tsql table column constraint must be uppercase
SQL
1 vote181 views1 answer
Rails run native ssql query
SQL
7 votes195 views1 answer
Set sequence value oracle
SQL
1 vote208 views1 answer
Oracle compile schema
SQL
37 votes228 views5 answers
FIND DUPLICATES IN COLUMN SQL
SQL
1 vote181 views1 answer
Oracle avg
SQL
0 vote245 views1 answer
Tsql find the value and count of the item that occurs the most in a column
SQL