/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

8 votes140 views1 answer
Mac restore mysql database command line
SQL
0 vote118 views2 answers
Add new column after in sql
SQL
1 vote132 views1 answer
Sql string function update replace
SQL
1 vote115 views1 answer
What is int in mysql
SQL
1 vote116 views1 answer
Table infromation in sql server
SQL
-1 vote133 views1 answer
Insert results of a stored procedure into a temporary table
SQL
0 vote128 views1 answer
How to select only a certain date sql
SQL
0 vote121 views1 answer
Cannot truncate a table referenced in a foreign key constraint (`video_clips`.`channel_clips`, CONSTRAINT `clips_fk` FOREIGN KEY (`clip_id`) REFERENCES `video_clips`.`clips` (`id`)) in sql]
SQL
0 vote131 views1 answer
Mysql subdate
SQL
0 vote135 views1 answer
Grant privileges mysql to database 1064
SQL