/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

3 votes325 views4 answers
Sql interview questions
SQL
0 vote204 views1 answer
Redudancy in SQL
SQL
0 vote291 views2 answers
Install phoenix
SQL
0 vote209 views1 answer
Recover deleted data from mysql database
SQL
0 vote213 views1 answer
Sql update subtract value
SQL
0 vote213 views2 answers
How to find max and min salary in sql
SQL
1 vote213 views1 answer
Increase existing date in sql
SQL
0 vote222 views1 answer
Sql new line output
SQL
0 vote219 views2 answers
How to list all values of a column that start with a letter in sql
SQL
13 votes197 views4 answers
Mysql get last row
SQL