0

I have a number of records that I would like to insert into a MYSQL table 'table1'. Some of them are duplicates.

I have a composite unique constraint made of 'FIELD A' and 'DUP_NUMBER'

so a group of records would look schematically like:

ID     FIELD A     DUP_NUMBER 
1       text1        0
2       text2        0
3       text2        1
4       text2        2
5       text3        0

one more time to actually insert it into the table. Is this possible if so how would I do it?

I am looking for a mysql select statement that would select only unique entries in FIELD a, but with the added proviso that you get the highest DUP_NUMBER .

The resultset should look like:

ID     FIELD A     DUP_NUMBER 
1       text1        0
4       text2        2
5       text3        0

Thanks in advance,

Bill

user1592380
  • 34,265
  • 92
  • 284
  • 515

3 Answers3

1

You can use this query:

SELECT `FIELD A`, MAX(`DUP_NUMBER`) AS `DUP_NUMBER`
FROM `table1`
GROUP BY `FIELD A`

Do you really need to have the data structured as it is? It looks like it can be simplified by removing the DUP_NUMBER column, assuming that the duplicate numbers are in the same order as the ID.

G-Nugget
  • 8,666
  • 1
  • 24
  • 31
1

try this

   select id , `field a` ,MAX(`DUP_NUMBER`) as dup_number  
   from table1 group by `FIELD A`

DEMO HERE

echo_Me
  • 37,078
  • 5
  • 58
  • 78
1

You can do it as follows:

SELECT field_a, max(dup_number) as dup
FROM table1
GROUP BY field_a

The max aggregation function, gives you the maximum of the duplicate number, while the group by gurantees thet only unique items are shown.

If you need to know also the id, you may do something like the following

SELECT t.*
FROM table1 t INNER JOIN (
    SELECT field_a, max(dup_number) as dup
    FROM table1
    GROUP_BY dup_number
) g ON t.field_a = g.field_a AND t.dup_number = g.dup
decden
  • 679
  • 4
  • 19