/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 vote102 views3 answers
Union in sql
SQL
0 vote120 views1 answer
Leftjoin in sql
SQL
-1 vote111 views1 answer
How to dump .csv file into mysql
SQL
0 vote119 views1 answer
Xamarin sql lite create table if not exist
SQL
0 vote112 views1 answer
Get date from timestamp in mysql
SQL
1 vote97 views1 answer
Mysql batch insert
SQL
0 vote109 views2 answers
Get value from a table an insert it with other values in another table sql
SQL
1 vote104 views1 answer
Show per page table in xampp
SQL
0 vote112 views2 answers
Sql regex vowel
SQL
0 vote103 views1 answer
Update one column from another column in same table
SQL