/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 vote154 views1 answer
What does the -p flag do in mysql cli
SQL
0 vote155 views1 answer
IS THEre any difference between using default and := in plsql
SQL
0 vote155 views2 answers
Collation in sql
SQL
39 votes208 views4 answers
Select duplicates in sql
SQL
0 vote186 views1 answer
Having all mysql
SQL
36 votes154 views3 answers
Add column in mysq
SQL
1 vote124 views2 answers
T sql get foreign key
SQL
0 vote126 views2 answers
Add new column after in sql
SQL
0 vote146 views1 answer
N file included from src/buffer.cpp:12: src/pyodbc.h:56:10: fatal error: 'sql.h' file not found #include <sql.h>
SQL
1 vote125 views1 answer
How to delete data from sql database in android
SQL