Search code examples
group-bykdb

Remove first n elements by group from table

Say I have the following table:

t:([]date:25#(.z.d+ til 5); travel:(5#`car),(5#`plane),(5#`bus),(5#`cycle),(5#`scooter); val:25?100)

date       travel  val
----------------------
2019.12.06 car     75 
2019.12.07 car     47 
2019.12.08 car     70 
2019.12.09 car     32 
2019.12.10 car     86 
2019.12.06 plane   29 
2019.12.07 plane   96

How do I remove the first n observations (assuming they do not start all on 2019.12.06) by travel column?

For instance, in that particular example, if n=1, I would only get entries where date>2019.12.06.

Solution

  • I would use following code snippet:

    n: 3;
select from t where i>({last[y]^y@x-1}[n];i) fby travel

    In above statement all row numbers are grouped by travel and first n of them are removed. {last[y]^y@x-1}[n] returns nth rownum value or last rownum, if n is larger than number of rows in group.