/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 vote272 views1 answer
Find usage of table in sql server
SQL
0 vote233 views1 answer
Month mysql
SQL
0 vote276 views1 answer
Mariadb cast null to 0
SQL
2 votes277 views1 answer
Mysql select default if null
SQL
2 votes275 views1 answer
Mariadb json select
SQL
0 vote271 views1 answer
Postgresql select join by date - Join rows where a timestamp value is equal to midnight of the date
SQL
0 vote257 views1 answer
Replace multiple string in query sql
SQL
0 vote259 views2 answers
Mysql to python pandas
SQL
0 vote220 views1 answer
Import csv to mysql
SQL
0 vote270 views1 answer
Selecting from a view SQL
SQL