How can we set a variable in select sql using "system.db.runQuery"?

The original table I wanna query contains no index, while I wanna add index to make the final display more user-friendly.

I first tried it in mysql and it worked like this which is great and convenient.

However, when I put the same sql query into “system.db.runQuery”, there exists an error in the sql query:

I also tried named query using the same way to set a variable but it failed too.

I know there are other ways that allow me to add index to the display table, but for me , to add it in the query is the most ideal method and I really wanna make it work.

Looking forward to your help, guys!!!

You’re running 2 queries in a single call. You can’t do that with system.db.runQuery()

Query 1
SET @i := 0;
Query 2
SELECT ......

Not sure if you can just call the runQuery twice, one for each statement, or if you need to begin a transaction for the session variable to work.

Okay, so I got curious and tested it out.
This is possible to do in a single statement.

	(@i:=@i+1) as 'index',
	t.time as 'time',
	t.v as 'v'
FROM send_h t, (SELECT @i:=0) itterator
WHERE t.time BETWEEN '2021-04-02 12:00:00' AND '2021-04-03- 12:00:00'

Thank you so much my dear friend!!! @Claus_Nielsen