/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

2 votes118 views1 answer
Get hour from date sql
SQL
18 votes111 views3 answers
Mysql number format
SQL
1 vote120 views1 answer
Oracle create index if not exists
SQL
24 votes132 views4 answers
Postgres delete database
SQL
0 vote114 views4 answers
Best data type to represent money in mysql
SQL
1 vote121 views1 answer
How to select department from table
SQL
0 vote129 views1 answer
Mysql dump for selected row
SQL
1 vote127 views2 answers
Oracle create synonym
SQL
0 vote124 views1 answer
Drop CHECK constraint sql
SQL
0 vote108 views1 answer
10000000000000000000 am to meters
SQL