Call a row in SQL command by RowHeader?

[jouky_d]

[ FlexSim 23.2.1 ]

Hello everyone,

I need to take a row of a Table to an array. For example:

	Col1	Col2
Row1	1	2
Row2	3	4

By indexRow

table[2] --> [3, 4]

Or by RowHeader:

table["Row2"] -- > [3,4]

I know I can obtain the array of a column doing:
Table.query("SELECT ARRAY_AGG([Col2]) FROM table")

However, in SQL the WHERE statement is only for Column. I don't know how to call the RowHeader as a column as:

Table.query("SELECT * FROM table WHERE Row = 2)

Or by RowHeader:
Table.query("SELECT * FROM table WHERE RowHeader = "Col2")

How can we obtain the row into an array? I tought to make a for loop to obtain all values into an array, but maybe SQL statement will be optimal.

Thank you!:D

[jason_lightfoot_adsk · Accepted solution]

You can try using ROW_NUMBER as described in the documentation.

[jouky_d]

Hello @Jason Lightfoot I tried this:

Table tab2 = Table.query("SELECT * FROM table WHERE ROW_NUMBER() = 2");

It returns an error. I can't find examples of ROW_NUMER or SQL Window Functions. I don't know where to apply those functions.

Can you write an example of it please?

As I deduced, the SQL Aggregation Functions are used on columns (e.g.: Table.query("SELECT ARRAY_AGG([Col2]) FROM table")).

But I don't know how to apply: SQL Window Functions, SQL Functions, GROUP BY.

Can you help me on understanding how and where to apply these sort of commands?

Thank you!

[jason_lightfoot_adsk]

For example:

Table.query("SELECT ROW_NUMBER AS rownum, * FROM GlobalTable1 WHERE ROW_NUMBER=3").cloneTo(Table("resultSet"));

ROW_NUMBER_GROUP_BY_Examples.fsm

[jouky_d]

Thank you!:D