/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

17 votes326 views6 answers
Get last record in sql
SQL
16 votes328 views3 answers
Jwt secret key generator laravel
SQL
0 vote315 views1 answer
How to avoid duplicate records in sqlite
SQL
1 vote324 views1 answer
Failed to stop mysqld.service: Unit mysqld.service not loaded.
SQL
0 vote323 views1 answer
Show database not empty tables postgres
SQL
0 vote336 views1 answer
List all indexes postgres
SQL
0 vote317 views1 answer
Get cpu details sql
SQL
16 votes332 views2 answers
Sql exists
SQL
0 vote334 views2 answers
Cambiar nombre tabla mysql
SQL
9 votes342 views4 answers
How to alter table column name in mysql
SQL