/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 vote181 views1 answer
Replace content value from old to new sql
SQL
2 votes161 views1 answer
Mysql list users on ubuntu
SQL
4 votes196 views1 answer
Describe table mysql
SQL
23 votes181 views4 answers
How to uninstall postgresql in ubuntu
SQL
3 votes175 views1 answer
Sql is not like
SQL
0 vote191 views1 answer
Sql date time query find
SQL
0 vote174 views2 answers
Alter table add multiple columns mysql
SQL
3 votes179 views3 answers
Mysqldump: couldn
SQL
-1 vote178 views3 answers
My sql alter table
SQL
25 votes203 views3 answers
Mysql command show tables
SQL