/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

6 votes196 views5 answers
Alter table oracle
SQL
0 vote197 views1 answer
Oracle sql log to console
SQL
49 votes308 views6 answers
Mysql add column to table
SQL
0 vote209 views1 answer
If column value is null then in mysql
SQL
0 vote230 views1 answer
Sql select lowest value row
SQL
8 votes255 views1 answer
Mac restore mysql database command line
SQL
0 vote212 views1 answer
How to list function in psql
SQL
5 votes228 views4 answers
Traduction
SQL
1 vote204 views1 answer
Mysql decimal allow negative values?
SQL
2 votes288 views1 answer
Get the mysql table columns data type mysql
SQL