Select and Sort a Window of Rows

Question

Given the following table of orders:

t:([] orderId:1001 1002 1003 1004 1005 1006 1007 1008 1009 1010;
      qty:500 200 1000 300 150 400 250 800 350 600;
      price:106.90 103.22 107.15 104.20 103.92 108.16 100.98 100.47 109.00 109.78)

Result:

orderId qty  price
-------------------
1001    500  106.9
1002    200  103.22
1003    1000 107.15
1004    300  104.2
1005    150  103.92
1006    400  108.16
1007    250  100.98
1008    800  100.47
1009    350  109
1010    600  109.78

Starting at row index 2, take the next 5 rows, and return them sorted by price in ascending order.

Expected result:

orderId qty  price
-------------------
1007    250  100.98
1005    150  103.92
1004    300  104.2
1003    1000 107.15
1006    400  108.16

Solution

`price xasc select [2 5] from t

Explanation

The bracket syntax in qSQL limits the rows a query returns.

Limiting the Number of Rows

select [5] from t

returns the first 5 rows, and select [-5] from t returns the last 5.

Specifying a Starting Row

select [2 5] from t

Here 2 is the starting row index and 5 is the number of rows to return, so this returns rows 2 through 6:

orderId qty  price
-------------------
1003    1000 107.15
1004    300  104.2
1005    150  103.92
1006    400  108.16
1007    250  100.98

`price xasc then sorts just those 5 rows by price, ascending, giving the expected result.

Why Not select [2 5;<price] from t?

The bracket syntax also accepts a sort specification after a semicolon: <price for ascending, >price for descending. But that sort is applied to the whole table first, and the row window is taken afterwards:

select [2 5;<price] from t

orderId qty  price
-------------------
1002    200  103.22
1005    150  103.92
1004    300  104.2
1001    500  106.9
1003    1000 107.15

These are rows 2 through 6 of the table after sorting it by price — a different question ("the 3rd to 7th cheapest orders") from the one asked here. Choose the form that matches what you need:

More Examples

select [10] from t            / first 10 rows
select [3 4] from t           / rows 3 through 6
select [3;>price] from t      / the 3 most expensive orders
select [-3;<price] from t     / the 3 most expensive orders, in ascending price order