/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 vote322 views1 answer
Mysql get date diff in months
SQL
4 votes302 views1 answer
Id primary key auto generated
SQL
0 vote290 views1 answer
How to check which sp is running in sql server
SQL
0 vote316 views1 answer
Get schema of table sql
SQL
0 vote298 views1 answer
How to get initials in sql
SQL
1 vote308 views1 answer
Cannot drop database because it is currently in use
SQL
0 vote353 views2 answers
Clauses in mysql
SQL
0 vote288 views1 answer
How to count with except in psql
SQL
47 votes370 views6 answers
Add column mysql
SQL
15 votes300 views2 answers
Sql order by multiple columns
SQL