How to display all the columns of the duplicate values in MYSQL

Question

I would like to display all the columns of the duplicate values in MYSQL.

I am selecting the duplicate values in MYSQL based on this post:Finding duplicate values in a SQL table.

MY sql statement looks like this:

    SELECT
        `subject`, `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`, COUNT(*)
    FROM
        `qz_question`
    GROUP BY
        `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`
    HAVING 
        COUNT(*) > 1

Instead of Grouping by the columns, I would like to display all the columns of all the duplicate values in the database.

I have tried this:

SELECT
        *
    FROM
        `qz_question`
    GROUP BY
        `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version`
    HAVING 
        COUNT(*) > 1

For example, for each unique row, there is a duplicate. It should display both the unique and the duplicate together at the same time.But this displays only one unique row. I want the duplicate as well.

`subject`, `topic`, `sub_topic`, `difficulty`,`question_number`,`question_version` — lakshmen, Jun 17 '15 at 13:44
I want to display all the duplicate values in my database. Edited my question — lakshmen, Jun 17 '15 at 14:07

score 0 · Answer 1 · answered Jun 17 '15 at 14:21

0

I think you are trying to do something like this:

SELECT  q1.*, q2.dups
FROM    qz_question q1
        JOIN ( SELECT   subject ,
                        topic ,
                        sub_topic ,
                        difficulty ,
                        question_number ,
                        question_version ,
                        COUNT(*) AS dups
               FROM     qz_question
               GROUP BY topic ,
                        sub_topic ,
                        difficulty ,
                        question_number ,
                        question_version
               HAVING   COUNT(*) > 1
             ) q2 ON q1.topic = q2.topic
                     AND q1.sub_topic = q2.sub_topic
                     AND q1.difficulty = q2.difficulty
                     AND q1.question_number = q2.question_number
                     AND q1.question_version = q2.question_version

answered Jun 17 '15 at 14:21

Giorgi Nakeuri

35,155
8
47
75

At times, it shows the rows that are not duplicates and I need to have delete functionality at the side. How do i do it? – lakshmen Jun 17 '15 at 14:28
At what times? May be you should add ordering at the end to see duplicates? One row may be at top of result and the duplicate may appear somewhere in the end. – Giorgi Nakeuri Jun 17 '15 at 14:35
But how to have the delete functionality at the side, I am using phpmyadmin for each row. – lakshmen Jun 17 '15 at 14:39
Original question didn't mentioned that you wanted to delete. What you want to leave and what you want to delete? – Giorgi Nakeuri Jun 17 '15 at 14:41
I don't want to delete using query but manually. So I need a delete button at each row. How do I do it? – lakshmen Jun 17 '15 at 14:42
@lakesh, I have no idea what you are talking about. This question is fully answered. Ask another question. I know nothing about php and phpadmin. – Giorgi Nakeuri Jun 17 '15 at 14:49

How to display all the columns of the duplicate values in MYSQL

1 Answers1