/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 vote177 views2 answers
How to drop a unique constraint in sql
SQL
1 vote183 views1 answer
Snowflake rename column name
SQL
0 vote230 views1 answer
Sql ssrs
SQL
0 vote168 views1 answer
Java input type sql date
SQL
0 vote276 views1 answer
Get statis values sql
SQL
4 votes173 views4 answers
Start mysql
SQL
8 votes221 views6 answers
Insert command in sql
SQL
0 vote173 views1 answer
Bigquery declare
SQL
3 votes167 views1 answer
Sql multiple column index
SQL
1 vote169 views1 answer
Found option without preceding group in config file: /etc/mysql/mariadb.conf.d/50-server.cnf at line: 1
SQL