/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 votes178 views1 answer
Increment integer in table sql
SQL
0 vote164 views1 answer
Alter precision
SQL
0 vote327 views1 answer
Mysql workbench Error Code: 2013. Lost connection to MySQL server during query
SQL
13 votes163 views5 answers
Select last row mysql
SQL
1 vote226 views1 answer
Cannot drop database because it is currently in use
SQL
2 votes195 views3 answers
Sql function
SQL
1 vote164 views1 answer
Mysql current time
SQL
1 vote186 views3 answers
Psql fatal database does not exist
SQL
0 vote175 views1 answer
How get data from database if id not found then search another column
SQL
0 vote159 views1 answer
Max length found in mysql
SQL