Documentation Visual SQL FAQs

Calculate row number in Visual SQL

If you joined before January 6, 2020, you might be using our old Data Explorer interface. Check out Calculate row number in Data Explorer for more relevant content.

It can often be handy to have row numbers when viewing data in tabular form to display information or to use in calculations.

Let’s take a look at how we can calculate row numbers in Visual SQL in order to generate a Table chart similar to the one below:

Table chart with row id added

To add a column with the row numbers to your chart, add a Formula Column Action, select Custom for the formula type, then use the following formula:
```
 rowid
```
Click Save to add the new column to your result set.
The newly added Formula Column is given the name “Custom Formula” by default, but you can rename it—for instance, to “Row Id”—for clarity.
If you want your new Row Id column to be the leftmost column in your Table chart, simply use a Reorder Action to quickly move it.
Your Row Id values will start at 1 and are calculated according to the order of your rows at the time you add the Formula Column.

If you want your Row Id values to populate based on a different row order, just add a Sort Rows Action before adding the Formula Column to reorder your results.

Once you do this, the Formula Column with your rowid formula will automatically be adjusted.

Now you just need to save your chart to save your changes.

Tip! You can use this Row Id column in a number of ways, for example as part of a CASE statement to set conditions depending on the Row Id value or in a Filter Rows Action to include or exclude some rows from your result set.