Column that increments it's value for each time a value is found in first column in MySQL

Question

I would like to create a column that increments each time the same value is found in the first column. These fields could be strings, or integers. Something like:

field1 | field2
cat | 1
fish | 1
dog | 1
cat | 2
fish | 2
cow | 1

My question is similar to these two (1), (2), but I do not want to INSERT or UPDATE if the key already exists. I want to delete the second record. So, something like DELETE FROM t1 WHERE field2 > 1.

This question (MySQL delete duplicate records but keep latest) is MOST similar to what I'm trying to do, except instead of having an integer auto-incremented ID column, I have another field called filename, which would be different for each repeated field1.

Is this called an aggregate function? How to accomplish this?

Every time *which* value is found. Also, you seem to be assuming an ordering of the rows but tables represent *unordered* sets. — Gordon Linoff, Sep 11 '20 at 18:28
Each time the value in `field1` is found set the value of `field2` to be incremented by 1 from the last time the `field1` value was found. The fields may be strings or whatever, it's not ordered, I understand that. — raw-bin hood, Sep 11 '20 at 18:30
write an insert tirgger that get the last number and increments it before inserting — nbk, Sep 11 '20 at 18:50
I'm not inserting data into the database. I'm attempting to write a delete statement that deletes the duplicates. — raw-bin hood, Sep 11 '20 at 18:52
Can't you alter the table ? And after deleting duplicates revert the alter to its original structure ? — Sujitmohanty30, Sep 11 '20 at 20:25
Seem this simulation: http://mysql.rjweb.org/doc.php/myisam2innodb#index_issue_2_column_pk — Rick James, Sep 13 '20 at 22:50

score 0 · Answer 1 · answered Sep 11 '20 at 18:28

0

What you seem to be asking for is counting a particular value. So, if you are trying to count 1 values and you have an ordering column, then:

select t.*, sum(field1 = 1) over (order by <ordering column>) as field2
from t;

However, your results suggests that you just want an enumeration of each independent value. That would be:

select t.*, row_number() over (partition by field1 order by <ordering column>) as field2
from t;

answered Sep 11 '20 at 18:28

Gordon Linoff

1,242,037
58
646
786

I can't use row_number() since I'm on MariaDB 5.5. – raw-bin hood Sep 11 '20 at 18:40
I'm not trying to simply get the count of times that value is present such as `SELECT field1, count(*) as field2 FROM t1 GROUP BY field1 HAVING field2 > 1 ORDER BY field2 DESC`. I can edit the example above to include a better. – raw-bin hood Sep 11 '20 at 18:47
I Googled the term `ordering column SQL` and found no references to this structure. Do you have a link to explain what that is? – raw-bin hood Sep 11 '20 at 18:50

Column that increments it's value for each time a value is found in first column in MySQL

1 Answers1