User-defined variables in MEMSQL?

Help Procedural SQL
1

Hi, I have a problem and would like to define variable to solve my problem, but this is not offered as far as I could read. In MySQL this works with user-defined variables:

This is my statement in MySQL:

SET @row_number = 0;
SELECT * FROM(
SELECT id, name, filter
FROM TABLE_X
) a
WHERE (@row_number:=@row_number + 1) % 5 = 1;

I solved this problem in MS SQL like this:

Select * FROM (
SELECT id, name, filter
FROM TABLE_X
)a
WHERE  abs(CHECKSUM(*)) % 5 =1;

I would like to divide the statement into several statements. If i have 10.000 rows and split it into 5 statements every statement have to read 2.500 rows.

It would be very helpfull if anybody have a good and performant solution for my problem.
i hope i could explain my issue.

thanks in advance,

Hamza

2

MemSQL currently does not support user-defined variable. However, there are a couple of things that you could do to achieve your goal of splitting up a group of rows into several sub-groups.

First, could you instead use where id % 5 = 1? If id is an auto increment column, then id would be a good thing to split on.

If that’s not an option, you could use the ROW_NUMBER window function (https://docs.memsql.com/sql-reference/v6.7/row_number/). The query in MemSQL would look like:

Select id, name, filter FROM (
    SELECT id, name, filter, ROW_NUMBER() OVER(ORDER BY id) as rowId
    FROM TABLE_X
) a
WHERE rowId % 5 =1;