/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

3 votes163 views1 answer
Apex call duration
SQL
4 votes131 views1 answer
List tables sqlite
SQL
3 votes117 views1 answer
Laravel database Using Named Bindings
SQL
0 vote113 views1 answer
Postgresql like
SQL
0 vote130 views1 answer
Sql query for login with email or username
SQL
0 vote109 views1 answer
Delete * from where id = 1;
SQL
1 vote117 views1 answer
Oracle first row
SQL
0 vote130 views1 answer
How to remove quotes from a string in ssis load file
SQL
0 vote108 views1 answer
How to search query in python3 sqlite3
SQL
0 vote125 views1 answer
Mariadb alter table add column if not exists example
SQL