/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 vote348 views1 answer
Oracle db create new schema
SQL
10 votes359 views2 answers
Linux command line import mysql database
SQL
0 vote404 views1 answer
Java sql timestamp now
SQL
5 votes358 views1 answer
Sql use database
SQL
0 vote421 views1 answer
Should I use the datetime or timestamp data type in MySQL?
SQL
3 votes370 views3 answers
Sql last week
SQL
1 vote377 views1 answer
Sql server list locks
SQL
0 vote304 views1 answer
Mysql collation for all languages
SQL
2 votes368 views1 answer
How to find constraints on a table in oracle
SQL
14 votes370 views1 answer
Sql value of string
SQL