Selecting subset according to the position

Divide the students into two classes according to their scores by using the snake algorithm, that is, Class 1 has students who ranks 1, 4, 5, 8 ... Class 2 has those who ranks 2, 3, 6, 7 ... .

SQL

Class 1:

    SELECT name,score
    FROM (SELECT name,score,
                 row_number() OVER(ORDER BY score desc) score_rank
          FROM score_table)
    WHERE mod(score_rank,4)=0 or mod(score_rank,4)=1
    ORDER BY score_rank

Class 2:

    SELECT name,score
    FROM (SELECT name,score,
                 row_number() OVER(ORDER BY score desc) score_rank
          FROM score_table)
    WHERE mod(score_rank,4)=2 or mod(score_rank,4)=3
    ORDER BY score_rank

The SQL record set is in random order, so a subquery is required to create the sequence number for rows before accessing the desired position.

SPL

A
1 =demo.query("select * from score_table").sort(score:-1)
2 =A1.step(4,1,4) Class 1
3 =A1.step(4,2,3) Class 2

SPL handles this task with ease as it can access the record directly according to the positions.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.