I would like to generate a new ID number for every new combination of column 1 and column 2. For example:
ID | column 1 | column 2
1 | peter | blue
2 | mark | red
1 | peter | blue
As there will be new rows added over time, with new values, this should be able to auto-update. I tried DENSE_RANK(), which seemed to work. But it gave an error when I put it as a statement in a calculated column, so I guess this is not possible? (Still very new to SQL).
Thanks for any help!
Error message: #1901 - Function or expression 'dense_rank()' cannot be used in the GENERATED ALWAYS AS clause of `ProductIdentifier
EDIT: What I basically want is to link a row to another table based on the 2 columns. I could also concatenate the two columns of course, but I read somewhere that doing this with string will be slower. It will ultimately be a big table with currently 200.000+ rows and growing to millions. Is this something I could/should do?
I don't understand. If you want
column1
/column2
to be unique, then they should be in their own table:This gives you the unique value for the pair that you seem to want.