/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 vote192 views1 answer
On update current_timestamp jpa
SQL
0 vote249 views3 answers
How to modify alter user root@localhost identified with mysql_native_password by properly
SQL
2 votes249 views1 answer
Select a certain number of entries from select mysql
SQL
0 vote183 views1 answer
Postgres call view
SQL
3 votes190 views2 answers
Sql end of month
SQL
1 vote256 views1 answer
Postgresql Insufficient privilege: 7 ERROR: permission denied for table
SQL
1 vote196 views1 answer
Rows to comma separated values in mssql
SQL
3 votes200 views1 answer
Increment integer in table sql
SQL
0 vote187 views1 answer
Get all employee of salary if more than in sql
SQL
0 vote183 views1 answer
Check mysql password with docker container magento 2
SQL