Select First,Last and Second Last Rows by Group

Question

Given the following table:

t:([] sym:`a`b`b`b`c`c`c`c;
      p:1 2 3 4 5 6 7 8)

Result:

sym p
-----
a   1
b   2
b   3
b   4
c   5
c   6
c   7
c   8

Using fby, write queries to return:

1. The first row for each sym
2. The last row for each sym
3. The second-last row for each sym

Solution

First row by symbol

select from t where i=(first;i) fby sym

Result:

sym p
-----
a   1
b   2
c   5

Last row by symbol

select from t where i=(last;i) fby sym

Result:

sym p
-----
a   1
b   4
c   8

Second-last row by symbol

select from t where i=({first -2#x};i) fby sym

Result:

sym p
-----
a   1
b   3
c   7

Alternative Solution

If a symbol only has one row and should not appear in the second-last result, return a null index for groups of length 1:

select from t where i=({$[1=count x;0N;first -2#x]};i) fby sym

Result:

sym p
-----
b   3
c   7

Explanation

The implicit column i represents the row index of each row in the table.

For the table t, the row indices are (shown as an extra column for illustration):

i sym p
-------
0 a   1
1 b   2
2 b   3
3 b   4
4 c   5
5 c   6
6 c   7
7 c   8

The fby operator applies a function within each group and broadcasts the result back to the original rows.

First Row by Symbol

(first;i) fby sym

returns, for every row, the first row index of that row's sym group:

0 1 1 1 4 4 4 4

Comparing this with i:

i=(first;i) fby sym

produces:

10001000b

so the query:

select from t where i=(first;i) fby sym

returns:

sym p
-----
a   1
b   2
c   5

Last Row by Symbol

(last;i) fby sym

returns, for every row, the last row index of that row's sym group:

0 3 3 3 7 7 7 7

So:

select from t where i=(last;i) fby sym

returns:

sym p
-----
a   1
b   4
c   8

Second-Last Row by Symbol

The expression:

{first -2#x}

takes the last two row indices from each group and returns the first of those two.

For example, for symbol b, the row indices are:

1 2 3

Applying:

-2#1 2 3

returns:

2 3

and:

first 2 3

returns:

2

That is the row index of the second-last row for symbol b.

So:

select from t where i=({first -2#x};i) fby sym

returns:

sym p
-----
a   1
b   3
c   7

For symbol a, there is only one row. Since:

-2#0

returns:

0 0

the expression still returns row 0.

Excluding Groups with Only One Row

If a group with only one row should not be included in the second-last result, use:

{$[1=count x;0N;first -2#x]}

This returns:

• a null (0N) when the group has only one row, which never equals any row index
• the second-last index otherwise

Therefore:

select from t where i=({$[1=count x;0N;first -2#x]};i) fby sym

returns only:

sym p
-----
b   3
c   7

Summary

Use fby with the implicit row index i when you want to select rows based on their position within each group:

select from t where i=(first;i) fby sym

select from t where i=(last;i) fby sym

select from t where i=({first -2#x};i) fby sym