/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 vote368 views3 answers
My sql alter table
SQL
0 vote364 views2 answers
Import .sql into postgres db command
SQL
1 vote376 views4 answers
Get week day from date in sql
SQL
2 votes341 views1 answer
Sql create database
SQL
11 votes375 views4 answers
Update row in mysql
SQL
0 vote385 views1 answer
How to set up an anonymous function to variable in swift
SQL
0 vote359 views2 answers
How to install mssql on mac
SQL
1 vote329 views1 answer
Sql select where id not exists in another table
SQL
7 votes378 views4 answers
Index in mysql
SQL
0 vote362 views1 answer
How to login to mysql in homestead
SQL