Record ID
Record ID adds a new column that numbers each row in your table, giving you an ID you can use to refer to that row in later steps. Choose the column name, starting number and whether the IDs are stored as BIGINT numbers or VARCHAR text.
Add row identifiers
This example starts with a small table with one person per row. Record ID adds a record_id column, assigning 1 to Ada's row, 2 to Lin's and 3 to Sam's.
Record ID interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| record_id | name |
|---|---|
| 1 | Ada |
| 2 | Lin |
| 3 | Sam |
- Change Start to
100. Therecord_idcolumn now contains 100, 101 and 102. - Change Column Name to
row_id. The first column in the output table is renamed torow_id. - Switch Type to
VARCHAR. The identifiers keep their sequence, but the output column now contains text instead of numbers.
When to use
Use Record ID when rows have no identifier and later steps need one, such as keeping rows apart in a Cross Tab or restoring the original order after a Sort.
Read the output
The output contains the original table with one new column at the beginning: record_id. With the default settings, Ada's row has ID 1, Lin's has ID 2 and Sam's has ID 3. The name values are unchanged.
The new identifier column contains BIGINT numbers by default. Choosing VARCHAR stores those identifiers as text. This setting only affects the new column; the original columns keep their data types.
How row order affects the IDs
Record ID assigns identifiers in the order it receives the rows. It doesn't use the values in name to decide which ID a row gets. For example, if Sam's row arrives first, it gets ID 1 with the default settings.
Each run begins again at Start, so these IDs aren't permanent identifiers for the people in the table. For numbering in a particular order, place Sort before Record ID.
For identifiers calculated from existing column values, see Formula.
Inputs and outputs
Inputs
1 port- InRequired
The table whose rows you want to assign identifiers to.
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
The original table with an identifier column added as the first column. Identifiers begin at Start and increase by one for each row.
Choose the value you need
Choose whether the new column stores identifiers as numbers or text. With Start set to 1, both options produce the sequence 1, 2, 3.
| Format | Example value | Use it for |
|---|---|---|
| BIGINTBIGINT | 1, 2, 3 | Keeping row identifiers as whole numbers. This is the default. |
| VARCHARVARCHAR | "1", "2", "3" | Working with another block or system that expects text identifiers. The quotes shown here indicate text and aren't included in the values. |
Configure the block
- Column Name
The name of the new column added to your table.
Default:
record_id- Start
The identifier assigned to the first row. Each following row gets the next number.
Default:
1- Type
Whether the new column stores identifiers as BIGINT numbers or VARCHAR text.
Default:
BIGINTOptions: BIGINT, VARCHAR.
Record ID FAQs
- Does Record ID need an input connection?
- Yes. Connect the table you want to number to In. Record ID adds an identifier to each row in that table.
- Where does Record ID put the new column?
- The new identifier column appears before the existing columns. All original rows and values are kept.
- What does Start control?
- Start sets the identifier for the first row. Each following row gets the next number. For example, setting Start to 100 produces 100, 101 and 102 for a table with three rows. Use a whole number.
- What is the difference between BIGINT and VARCHAR identifiers?
- BIGINT stores the identifiers as whole numbers. VARCHAR stores them as text. Both use the same sequence; choosing VARCHAR doesn't add leading zeros or a prefix.
- Will a row keep the same ID when the data changes?
- Not necessarily. Each time Record ID runs, it numbers the input rows again, beginning at Start. If the rows arrive in a different order, or rows are added or removed, an existing row may get a different ID.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.