/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

-1 vote252 views1 answer
Django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.13 or newer is required; you have 0.8.0.
SQL
3 votes253 views1 answer
How to check schema privileges in oracle
SQL
0 vote262 views1 answer
Select count concat string sql server
SQL
3 votes237 views4 answers
Show columns from table
SQL
1 vote236 views1 answer
Mysql order by desc null last
SQL
2 votes308 views1 answer
Sql compare strings
SQL
-1 vote267 views1 answer
Can pandas fetch data from sql
SQL
0 vote247 views1 answer
Find largest table in mysql database
SQL
1 vote281 views1 answer
Postgres see sequence current state
SQL
1 vote264 views1 answer
Postgres check blocking
SQL