/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 vote295 views1 answer
Mysql group by range
SQL
0 vote300 views1 answer
Create dump file mysql workbench
SQL
3 votes307 views1 answer
Start mysql server by terminal in linux
SQL
0 vote301 views1 answer
How to select an index in oracle sql
SQL
0 vote295 views1 answer
Sql server system messeges
SQL
5 votes318 views3 answers
DBMS_OUTPUT.PUT_LINE
SQL
6 votes305 views3 answers
Update with inner join postgres
SQL
51 votes401 views6 answers
Change column name sql
SQL
0 vote280 views1 answer
If null don't update
SQL
0 vote385 views1 answer
Restart serial number for postgres
SQL