Question 181184 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I return only duplicate rows with a lower identifier in MySQL database?

I have a MySQL database that has a table with multiple duplicate records. The table has 4 columns e.g. id, post_id, post_key and post_value. I am able to return the duplicate entries with the query below.

SELECT *
FROM table1 ppm
WHERE ppm.post_key = 'some_value'
AND ppm.post_id
IN
(SELECT pm.post_id FROM table1 pm
WHERE pm.post_key = 'some_value'
GROUP BY pm.post_id HAVING COUNT(pm.post_id) > 1)

It produces an output that looks like:

id, post_id, post_key, post_value
1, 2, key1, value1
2, 2, key1, value2
3, 2, key1, value3

Since there could be multiple repeating post_id e.g. 2, I’d like to only display the lower id values. Using the example above, it would only show the result below since 3 is the larger number than the previous values.

id, post_id, post_key, post_value
1, 2, key1, value1
2, 2, key1, value2

How can I return duplicate rows that have a lower id?

The current query returns all duplicate rows. I’d like to only have duplicate rows that have a lower id.

Tags:

2

Answers


  1. 0

    Use a self-join that checks that the ID is lower in the ON condition.

    SELECT DISTINCT t1.*
FROM table1 AS t1
JOIN table2 AS t2 ON t1.post_id = t2.post_id AND t1.id < t2.id
WHERE t1.post_key = 'some_value' AND t2.post_key = 'some_value'

    I added DISTINCT in case there are multiple duplicates, this will only return one of each pair.

    Login or Signup to reply.
  2. 0 
    SELECT id, post_id, post_key, post_value
FROM (
    select *,
       row_number() over (partition by post_id order by id desc) rn
    from `table1`
) t
WHERE rn <> 1

    See it work here:

    https://dbfiddle.uk/QZf1xFN8

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search