http://docs.memsql.com/5.0/concepts/window_functions/
I checked the doc linked above and came up with several possible SQL functions but had no luck with any of them. These are just a few:
select row_number() from test;
select row_number() over (order by _id) from test;
select row_number() over {order by _id} from test;
select row_number() over order by _id from test;
select row_number() over (order by _id) as row_num from test;
select row_number over order by _id as row_num from test;
All give me ERROR 1064 (42000): You have an error in your SQL syntax;
MemSQL version is 5.5.8
And of course select _id from test; works fine?
So what is the exact syntax to use? Or is there actually no such thing in MemSQL?
The version string you see is a mysql compatibility version, which I assume you got by doing SELECT version();. To correctly check your MemSQL version, use SHOW VARIABLES WHERE Variable_name = 'memsql_version';.
Window functions are new in MemSQL 5, leading me to believe you aren't running MemSQL 5. That wouldn't be odd, as 5 is still in beta (as of 2016-03-16). You can get the beta here.
In his reply to your question, dnoeth correctly states that queries #2 and #5 are valid. For due diligence, I have ran these queries against my local build of MemSQL, and both of them work.
memsql> create table test(_id int);
Query OK, 0 rows affected (0.01 sec)
memsql> insert into test values (1), (2), (3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
memsql> select row_number() over (order by _id) from test;
+----------------------------------+
| row_number() over (order by _id) |
+----------------------------------+
| 1 |
| 2 |
| 3 |
+----------------------------------+
3 rows in set (0.00 sec)
memsql> select row_number() over (order by _id) as row_num from test;
+---------+
| row_num |
+---------+
| 1 |
| 2 |
| 3 |
+---------+
3 rows in set (0.01 sec)
memsql> SHOW VARIABLES WHERE Variable_name = 'memsql_version';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| memsql_version | 5.0 |
+----------------+-------+
1 row in set (0.00 sec)