Skip to content

Instantly share code, notes, and snippets.

@Hazem-Ben-Khalfallah

Hazem-Ben-Khalfallah/duplicate_rows.sql

Created December 30, 2019 13:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Hazem-Ben-Khalfallah/a605d60e87b64a852189793e599238b6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Hazem-Ben-Khalfallah/a605d60e87b64a852189793e599238b6 to your computer and use it in GitHub Desktop.
Download ZIP
[Detect duplicate rows] #sql #mysql
Raw
duplicate_rows.sql
# display duplicate rows
SELECT
*
FROM (
SELECT
person_id,
@rank := IF( @email = email and @firstname = firstname, @rank + 1, 1) AS row_number,
@email := email as email,
@firstname := firstname as firstname
FROM
person
ORDER BY firstname, email DESC
) rankings
ORDER BY firstname, email, row_number;
# remove duplicate rows
delete from person
where person_id in (
SELECT
person_id
FROM (
SELECT
person_id,
@rank := IF( @email = email and @firstname = firstname, @rank + 1, 1) AS row_number,
@email := email as email,
@firstname := firstname as firstname
FROM
person
ORDER BY firstname, email DESC
) rankings
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment