/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 vote147 views3 answers
Sql server obtener fecha en formato dd/mm/yyyy
SQL
0 vote202 views1 answer
BigQuery Remove Duplicate Keys From Table
SQL
2 votes164 views1 answer
SELECT LOCKED FROM public.databasechangeloglock WHERE ID=1
SQL
0 vote155 views1 answer
Get last three characters in mysql column
SQL
0 vote181 views1 answer
Implode in sql query
SQL
0 vote123 views1 answer
Postgresql add leading zeros
SQL
0 vote202 views1 answer
Migrations.RunSQL
SQL
-1 vote163 views2 answers
Installing mysql on centos 7
SQL
0 vote169 views1 answer
Query to count the number of rows in a table in sqlalchemy
SQL
0 vote140 views1 answer
Get only first row mysql
SQL