/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 vote277 views1 answer
Configure MYSQL to run on another port number, other than the default port 3306 ubuntu linux
SQL
1 vote276 views4 answers
Sql now
SQL
0 vote276 views1 answer
Distinct data types in a table sql query
SQL
0 vote279 views1 answer
Create tables from xsd to sql server database c#
SQL
1 vote229 views1 answer
Mysqldump: couldn't execute 'select column_name,
SQL
0 vote271 views1 answer
Mysql default uuid
SQL
2 votes264 views1 answer
Query to find table size in oracle 12c
SQL
6 votes274 views5 answers
Alter table oracle
SQL
1 vote267 views1 answer
Oracle rolling back transactions
SQL
-1 vote297 views2 answers
Installing mysql on centos 7
SQL