Define a unique ID (serial number) based on values in a field

Question

I'm trying to generate a unique serial number for a dataset I have, for example, something like this:

As shown, I would like to have a serial field, called SERIAL in this case, that increments based on the times the value in the column FIELD NAME is repeated. The order in which the serial is given is unimportant, I just want to have a unique identifier there, to then add an alphanumeric value based on another table.

Is there any automatic way to achieve this?

Answer 1

Use this expression, where you have to replace color (end of line 2) with your fieldname:

array_find (
    array_sort(array_agg ($id, group_by:="color")),
    $id
)+1

Answer 2

You can create a Virtual Layer. Replace "color" with the name of your color field and 'Random points' with the name of your layer:

select  *, 
        row_number() over(partition by "color") as "SERIAL"
from 'Random points'

A new temporary layer will be created which you can right click and save:

Answer 3

You can also use the Processing > Toolbox > Vector Table > Add Autoincremental Field tool.

To use the tool in your situation, set Field name = SERIAL, Start values at = 1, and Group values by = FIELD NAME.

The tool has advanced features including modulus values, NULL values, and sort ordering.