/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 vote161 views1 answer
Jooq convert using gbk
SQL
1 vote164 views2 answers
How to check username in mysql command line
SQL
2 votes175 views1 answer
Change primary key sql
SQL
0 vote122 views1 answer
Check if due date is less than todays date sql
SQL
2 votes174 views1 answer
Can you use a where clause for a child query
SQL
0 vote162 views1 answer
Drop all tables db2
SQL
-1 vote142 views1 answer
MySQL said: Documentation #1010 - Error dropping database errno: 39 "Directory not empty")
SQL
0 vote196 views3 answers
Drop table operation failed on table 'TableHistory' because it is not supported operation on system-versioned temporal tables.
SQL
1 vote178 views1 answer
Adding a check statement in sql
SQL
0 vote185 views2 answers
Nodejs mysql convert to plain object
SQL