Mysql – How to avoid duplicate entries with SELECT statement

MySQLselect


+----+----------------------+---------+---------+---------------------+-------+
| id | translation          | id_word | id_user | added               | tuser |
+----+----------------------+---------+---------+---------------------+-------+
| 17 | допомагати           |       4 |       1 | 2013-08-29 14:52:20 |     2 |
| 17 | допомагати           |       4 |       1 | 2013-08-29 14:52:20 |     1 |
|  5 | когось               |       1 |       1 | 2013-08-27 23:35:09 |     1 |
|  4 | хто-небудь           |       1 |       1 | 2013-08-27 23:35:09 |  NULL |
|  1 | хтось                |       1 |       1 | 2013-08-27 23:34:17 |     2 |
|  1 | хтось                |       1 |       1 | 2013-08-27 23:34:17 |     1 |
+----+----------------------+---------+---------+---------------------+-------+

As you can see, I have duplicate entries in id. I have to avoid duplicate entries if:

There is a row with a tuser = 1, then I have to remove other entries with the same id, id_user and id_word from result.
If in rows (with id, id_user and id_word) no tuser = 1, it must show all values.

I need only one unique row if tuser = 1 for each unique combination id, id_word, id_user

Best Answer

SELECT a.*                                -- select all rows
FROM tableX  a                            -- from table
WHERE a.tuser = 1                         -- where either tuser = 1
   OR NOT EXISTS                          -- or there is no other 
      ( SELECT *                          -- row 
        FROM tableX  b                    -- in the table
        WHERE b.tuser = 1                 -- with tuser = 1
          AND b.id = a.id                 -- and same "id",
          AND b.id_user = a.id_user       -- same "id_user"
          AND b.id_word = a.id_word       -- and same "id_word"
      ) ;

Related Solutions

MySQL – SELECT and UPDATE in a Single Query for Duplicate Records

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example:

INSERT INTO Contacts
    (number, name, city)
VALUES
    (1234567892, 'Earl Grey', 'Salem'),
    (3333333333, 'Rich Malfoy', 'Sacramento'),
    (7656453248, 'Bill Gates', 'New York'),
    (5555555555, 'James McGill', 'Boston'),
    (6666666666, NULL, 'Baton Rouge')
ON DUPLICATE KEY UPDATE
    name = COALESCE(VALUES(name), name),
    city = COALESCE(VALUES(city), city) ;

Test at SQLfiddle

MySQL – How to Perform Aggregate Functions on Duplicate Column Values

It seems like your query needs to be something like:

SELECT 
  column_with_duplicates
  , AVG(column_to_aggregate) AS column_to_aggregate -- AVG does the math, AS gives the name
FROM <table name> 
GROUP BY 
  column_with_duplicates;

More information on creating aliases (the AS keyword).

More information on GROUP BY

More information on the AVG function with a handy example that would have served you well.

Since you had a question about the GROUP BY clause in the comments below...

As I understand it MySQL has some quirks with it's GROUP BY rules. Those quirks not withstanding, I think of a simple GROUP BY clause, like the one in the query above, as sorting the result set into buckets. So in our query above there would be one bucket for each unique value in the column_with_duplicates column. So each value in the column_to_aggregate would get dropped into the proper bucket. Once all the rows/values are sorted into their buckets then an average is taken of all the values in each bucket individually.

Of course this is just a conceptual model, but it should give you a basic understanding of the role of a GROUP BY clause.

Best Answer

Related Solutions

MySQL – SELECT and UPDATE in a Single Query for Duplicate Records

MySQL – How to Perform Aggregate Functions on Duplicate Column Values

Related Question