CODEWITHSUNDEEP
kdb+
Peter Lucas
Peter Lucas

Reputation: 1991

Select all rows in a KDB Table between two dates but only take the row with maximum value in a specific column

Day 1 on selecting data from KDB. I'd like to select all rows in table quote_flat for each unique value of quote_id but where only the max value of ammendment_no is shown. Each quote_id as 1 to n ammendment_no. I'm only interest in the final (max) ammendment_no.

Sample Data

time                            quote_id    amendment_no
2019.08.14D23:15:40.353188000   188484088   1
2019.08.14D23:15:47.234021000   188484088   2
.
.
.
2019.08.14D23:15:47.234026000   188484088   25

Query returns:

2019.08.14D23:15:47.234026000   188484088   25

Upvotes: 0

Views: 684

Answers (2)

Jamie Carton
Jamie Carton

Reputation: 71

You're looking to use fby, the following statement will do what you're looking for:

select from quote_flat where amendment_no=(max;amendment_no)fby quote_id

https://code.kx.com/v2/ref/fby/ I think that it's also worth mentioning that using column names that contain special characters such as quote_id should be avoided as _ is the cut/drop operator in kdb.

Upvotes: 1

terrylynch
terrylynch

Reputation: 13572

I think you want to:

select from quote_flat where ammendment_no=(max;ammendment_no) fby quote_id

Upvotes: 2

Related Questions