/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 views1 answer
Set column in all row mysql
SQL
0 vote203 views1 answer
Mysql error 1114 (hy000) the table is full
SQL
0 vote144 views1 answer
Mysql convert charset
SQL
4 votes149 views1 answer
Oracle select first 10 rows
SQL
-1 vote170 views5 answers
Operator in sql
SQL
3 votes139 views2 answers
Mysql create database
SQL
0 vote150 views1 answer
Mysql get longest string in column
SQL
0 vote152 views1 answer
Ffeathers express mysql application users login
SQL
4 votes161 views3 answers
Is not null sql
SQL
1 vote139 views1 answer
Delete triger pl pg sql
SQL