CODEWITHSUNDEEP
kdb+
Robert Kubrick
Robert Kubrick

Reputation: 8713

How can I select the last element of a kdb table for a set of unique column combinations?

I have this kdb table:

ts              ticker eid          bid     ask
--------------------------------------------------
09:30:00.001    TIF    NASDAQ       781700  792500
09:30:00.001    MRVL   BATSX        114500  118200
09:30:00.001    AVP    BATSX        239100  240100
09:30:00.001    AVP    ARCA         238000  239900
09:30:00.002    TIF    ARCA         780400  790400
09:30:00.002    AVP    BATSX        239100  239800
09:30:00.003    TIF    NYSE         783700  784000
09:30:00.003    TIF    ARCA         783400  790400
09:30:00.004    AVP    NYSE         239300  240000
09:30:00.004    TIF    NASDAQ       783500  792500
09:30:00.006    MRVL   BATSX        114500  117600
..

How can I select the last row of each ticker,eid pair? For this subset that would be:

09:30:00.001    AVP    ARCA         238000  239900
09:30:00.002    AVP    BATSX        239100  239800
09:30:00.003    TIF    NYSE         783700  784000
09:30:00.003    TIF    ARCA         783400  790400
09:30:00.004    AVP    NYSE         239300  240000
09:30:00.004    TIF    NASDAQ       783500  792500
09:30:00.006    MRVL   BATSX        114500  117600

Upvotes: 1

Views: 10989

Answers (3)

Sergey V
Sergey V

Reputation: 231

Select without column list does this by default:

select by ticker,eid form t

Upvotes: 4

user2242865
user2242865

Reputation: 567

You can aggregate over multiple fields.

select count i by ticker, eid from t

select last ts, last bid, last ask by ticker, eid from t

You will have to reorder your columns a little to exactly match your output, but thats trivial.

Upvotes: 1

user1895961
user1895961

Reputation: 1186

The following should be what you're looking for:

q)select from tbl where i=(last;i) fby ([]ticker;eid)

Upvotes: 3

Related Questions