/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 vote121 views1 answer
Sql beginning of previous month
SQL
42 votes216 views6 answers
Rename table sql server
SQL
0 vote143 views1 answer
How to find top 3 salary in sql
SQL
0 vote151 views1 answer
Oracle job session
SQL
0 vote190 views1 answer
Sqlite display headers on columns
SQL
0 vote143 views1 answer
Creating a simple notify in postgresql
SQL
0 vote125 views1 answer
SQL server select to get sum of hours
SQL
1 vote140 views3 answers
Remove unique key from a table
SQL
0 vote195 views2 answers
Wordpress change url in database
SQL
-2 votes145 views2 answers
Union all in sql
SQL