How do I create a table with multiple auto incrementing values?

Question

I would like to have an autoincrementing string in my BigQuery table. This seems like a good way of doing it:

CREATE TABLE dbo.YourTable
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    CompanyID AS '789-' + RIGHT('000000' + CAST(ID AS VARCHAR(7)), 7) PERSISTED,
    .... your other columns here....
)

However I actually want a single column based on multiple such counts with different prefixes.

So in the example a new record comes in and gets the label 789-00056. For my data if I have an a-record I want the label to be a-[running count of a's + 1], for b: b-[running count of b's + 1].

The data would look like this:

record type	label
a	a-1
a	a-2
b	b-1
a	a-3

Answer 1

Here is how:

 SELECT field1, field2 ,field3, 

ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field3 ASC) AS my_label

 FROM table