/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

5 votes375 views2 answers
Postgres update with if condition query
SQL
1 vote362 views1 answer
How to show index type in postgresql
SQL
0 vote352 views1 answer
Mostrar datos de tablas relacionadas mysql kjava
SQL
2 votes349 views1 answer
Mariadb JSON_EXTRACT
SQL
0 vote377 views1 answer
Top 3 salary in sql
SQL
1 vote396 views1 answer
Mysql find most common value
SQL
0 vote351 views1 answer
Sql get user account
SQL
1 vote386 views1 answer
Postgresql how to show table names
SQL
0 vote375 views1 answer
Sql make sure every user is unqiuew
SQL
2 votes354 views1 answer
Set username and password for postgresql database
SQL