mysqldistinctdeduplication

Remove duplicates SQL while ignoring key and selecting max of specified column

I have the following sample data:

| key_id | name  | name_id | data_id |
+--------+-------+---------+---------+
|   1    | jim   |   23    |   098   |
|   2    | joe   |   24    |   098   |
|   3    | john  |   25    |   098   |
|   4    | jack  |   26    |   098   |
|   5    | jim   |   23    |   091   |
|   6    | jim   |   23    |   090   |

I have tried this query:

INSERT INTO temp_table
SELECT
DISTINCT @key_id,
name,
name_id,
@data_id FROM table1,

I am trying to dedupe a table by all fields in a row.

My desired output:

| key_id | name  | name_id | data_id |
+--------+-------+---------+---------+
|   1    | jim   |   23    |   098   |
|   2    | joe   |   24    |   098   |
|   3    | john  |   25    |   098   |
|   4    | jack  |   26    |   098   |

What I'm actually getting:

| key_id | name  | name_id | data_id  |
+--------+-------+---------+----------+
|   1    | jim   |   23    |   NULL   |
|   2    | joe   |   24    |   NULL   |
|   3    | john  |   25    |   NULL   |
|   4    | jack  |   26    |   NULL   |

I am able to dedupe the table, but I am setting the 'data_Id' value to NULL by attempting to override the field with '@'

Is there anyway to select distinct on all fields and while keeping the value for 'data_id'? I will take the highest or MAX data_id # if possible.

Solution 
  • RENAME TABLE myTable to Old_mytable,
myTable2 to myTable
INSERT INTO myTable
SELECT *
FROM Old_myTable
GROUP BY name, name_id;

    This groups my tables by the values I want to dedupe while still keeping structure and ignoring the 'Data_id' column