/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

-1 vote317 views1 answer
Adding a new table psql
SQL
1 vote338 views1 answer
Search for replace in mysql
SQL
9 votes334 views5 answers
Sql limit
SQL
9 votes357 views2 answers
Convert data type in sql
SQL
0 vote306 views1 answer
Sqlalchemy.exc.DBAPIError: (ibm_db_dbi.Error) ibm_db_dbi::Error: SystemError('<built-in function connect> returned NULL without setting an error')
SQL
0 vote348 views1 answer
Data directory postgresql
SQL
-1 vote345 views4 answers
Postgres between dates
SQL
1 vote304 views1 answer
Sql comment header
SQL
3 votes310 views1 answer
How to remove characters from string in mysql
SQL
2 votes326 views2 answers
How to check data type in sql server
SQL