4

How can I extract the first n rows from each group? For example: for table bb: ([]sym:(4#`a),(5#`b);val: til 9)

  sym   val
  -------------
  a     0
  a     1
  a     2
  a     3
  b     4
  b     5
  b     6
  b     7
  b     8

How can I select the first 2 rows of each group by sym?

Thanks

Improve this question
0

2 Answers 2

Reset to default
5

Can use fby:

q)select from bb where ({x in 2#x};i) fby sym
sym val
-------
a   0
a   1
b   4
b   5
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

No problem. However be aware that this solution (along with the other one posted) assumes your table is in-memory. For on-disk queries it would obviously be a bit different
Is it possible to update the query so that it'll 1) only return the first 100 records if a group has more than 100 records 2) return all the records if the number of records for a given group is less than 100. Something like select [100] from ...
Found one solution like this: select from bb where ({x in min[100,count[x]]}#x) fby sym
The original answer already does this! For example: select from ([]sym:abbb;val:1 2 3 4) where ({x in 2#x};i) fby sym
returns all the rows for a as there are less than 2, returns only two rows of b when there are more than 2
2

You can try this:

q)select from t where i in raze exec 2#i by sym from t
sym val
-------
a   0
a   1
b   4
b   5
Improve this answer

2 Comments

Thanks. This one works but the fby solution above seems more intuitive to me. ;-)
I do agree since my solution will have to scan the table twice :D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.