0

I need to select the duplicate records in this table except one.

Example Data

+-------+-------+------+
| nid   | vid   | uid  |
+-------+-------+-------
| 701   |   209 |   11 |
| 701   |   210 |   11 |
| 701   |   211 |   11 |
| 701   |   212 |   1  |
| 701   |   213 |   1  |
| 701   |   214 |   97 |
| 701   |   215 |   97 |
+-------+-------+------+

Expected Results

I need to get this result with where condition for nid such as 701.

+-------+-------+------+
| nid   | vid   | uid  |
+-------+-------+-------
| 701   |   210 |   11 |
| 701   |   211 |   11 |
| 701   |   213 |   1  |
| 701   |   215 |   97 |
+-------+-------+------+

Any help would be appreaciated

4
  • Specify MySQL version (5 or 8?).\
    – Akina
    Oct 10, 2019 at 11:49
  • @Akina - It's version 5.7
    – RajeevK
    Oct 10, 2019 at 12:34
  • If some uid has 4 duplicates - does you need 3 output records with this uid? Is vid unique over table, over nid, or no such restrictions?
    – Akina
    Oct 10, 2019 at 12:55
  • Define duplicate -- Every row in your sample is different.
    – Rick James
    Oct 16, 2019 at 23:28

2 Answers 2

2

In the subquery you select the rows you want to keep. With the outer query you select all rows that are not in your subquery.

SELECT 
t.*
FROM
t
LEFT JOIN (
    SELECT
    MIN(nid) AS nid, MIN(vid) AS vid, uid
    FROM t
    GROUP BY uid
) t2 USING(nid, vid, uid)
WHERE t2.uid IS NULL;

Note that this would also not display rows where there are no duplicates. To display them as well you just don't select them in the subquery:

SELECT 
t.*
FROM
t
LEFT JOIN (
    SELECT
    MIN(nid) AS nid, MIN(vid) AS vid, uid
    FROM t
    GROUP BY uid
    HAVING COUNT(*) > 1
) t2 USING(nid, vid, uid)
WHERE t2.uid IS NULL;
11
  • Both of them returns all resultset :(
    – RajeevK
    Oct 10, 2019 at 10:50
  • Not for me. See it working live in an sqlfiddle: sqlfiddle.com/#!9/45db63/3/0
    – tombom
    Oct 10, 2019 at 10:53
  • @RajeevK Also works with your sample data: sqlfiddle.com/#!9/34dbe/2/0
    – tombom
    Oct 10, 2019 at 11:04
  • I have tried replicating with a sample data I am working here, but that doesn't return the same like yours
    – RajeevK
    Oct 10, 2019 at 11:07
  • If I need to add condition for "nid" such as 701, where can I put that? Adding that in end of statement after IS NULL not giving proper result
    – RajeevK
    Oct 10, 2019 at 11:21
1
SELECT t1.*
FROM sourcetable t1
WHERE EXISTS ( SELECT 1
               FROM sourcetable t2
               WHERE (t1.nid, t1.uid) = (t2.nid, t2.uid)
                 AND t1.vid > t2.vid )

Logic: return records for which there is at least one record with the same nid and uid and lower vid. So the record with the least vid will be skipped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.