-1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
schoon
  • 2,858
  • 3
  • 46
  • 78
  • 1
    Bigquery isn't really designed to be a transactional database like this. Can you explain what the underlying need is? Does it need to be deterministic, or just a number for a specific query? If the later, you can just use row_number(). – Mike Karp Apr 28 '22 at 18:24
  • Thanks Mike. It is just keeping a tally count of each type of record. Can you explain what you mean by ' deterministic, or just a number for a specific query?' please? – schoon Apr 29 '22 at 08:37
  • Meaning to you need that number to be the same for that row forever, or are you just using it for counting purposes? – Mike Karp Apr 29 '22 at 20:03

1 Answers1

0

Here is how:

 SELECT field1, field2 ,field3, 

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

 FROM table
schoon
  • 2,858
  • 3
  • 46
  • 78