Get first row from a group

[carfield1]

Hi, if I like to select the last row from a group, I can just do

select by xxx from table

if there similar syntax to select the first row? Other than using "first" for all elements?

[kuentang]

Something like this?

select by xxx from reverse table

Kim

Von: personal-kdbplus@googlegroups.com [mailto:personal-kdbplus@googlegroups.com] Im Auftrag von Carfield Yim
Gesendet: Dienstag, 5. Januar 2016 01:55
An: Kdb+ Personal Developers
Betreff: [personal kdb+] Get first row from a group

Hi, if I like to select the last row from a group, I can just do

select by xxx from table

if there similar syntax to select the first row? Other than using "first" for all elements?

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To post to this group, send email to personal-kdbplus@googlegroups.com.
Visit this group at https://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.

[carfield]

Thanks, yes, but if reverse a whole table expensive?

[mkrmkr]

you don't need to reverse the table. a function solution will work best. here's an example:

q)t:([]a:1 1 1 2 2 2;b:`a`b`c`d`e`f;c:10 20 30 40 50 60)

q)t

a b c

------

1 a 10

1 b 20

1 c 30

2 d 40

2 e 50

2 f 60

q)select by a from t

a| b c

-| ----

1| c 30

2| f 60

q)/is the same as

q)select last b,last c by a from t

a| b c

-| ----

1| c 30

2| f 60

q)/so for the first row

q)select first b,first c by a from t

a| b c

-| ----

1| a 10

2| d 40

q)/but for lots of columns that's tedious so

q)?[`t;();enlist[`a]!enlist`a;(cols[`t]except`a)!(first,/:cols[`t]except`a)]

a| b c

-| ----

1| a 10

2| d 40

q)/which you could make into a little utility

q)firstrow:{[t;g]?[t;();enlist[g]!enlist g;(cols[t]except g)!(first,/:cols[t]except g)]}

q)firstrow[t;`a]

a| b c

-| ----

1| a 10

2| d 40

q)

[TerryLynch]

What's wrong with

select from table where i=(first;i) fby keycol

Just have to be careful of using "i" with partitioned tables.

Also, the above can be easily changed to grab last, or first 2 rows per key etc. And can be used for multiple keys

select from table where i=(first;i) fby ([]keycol1;keycol2)

[Aaron_Davies]

note that this solution is the only one that works on partitioned tablescan be shortened and generalized to work with multi-column group as{?[x;();y!y,:();c!first,/:c:(cols x)except y]}(just to throw another one out there, there's also {first each'xxgroup y} — still won't with partitioned tables though, and it'sprobably the most expensive in both time and space)On Wed, Jan 6, 2016 at 4:43 PM, mkrmkr wrote:> you don't need to reverse the table. a function solution will work best.> here's an example:>> q)t:([]a:1 1 1 2 2 2;b:`a`b`c`d`e`f;c:10 20 30 40 50 60)> q)t> a b c> ------> 1 a 10> 1 b 20> 1 c 30> 2 d 40> 2 e 50> 2 f 60> q)select by a from t> a| b c> -| ----> 1| c 30> 2| f 60> q)/is the same as> q)select last b,last c by a from t> a| b c> -| ----> 1| c 30> 2| f 60> q)/so for the first row> q)select first b,first c by a from t> a| b c> -| ----> 1| a 10> 2| d 40> q)/but for lots of columns that's tedious so> q)?[`t;();enlist[`a]!enlist`a;(cols[`t]except`a)!(first,/:cols[`t]except`a)]> a| b c> -| ----> 1| a 10> 2| d 40> q)/which you could make into a little utility> q)firstrow:{[t;g]?[t;();enlist[g]!enlist g;(cols[t]except> g)!(first,/:cols[t]except g)]}> q)firstrow[t;`a]> a| b c> -| ----> 1| a 10> 2| d 40> q)>>>>> On Wednesday, January 6, 2016 at 11:15:43 AM UTC-5, Carfield Yim wrote:>>>> Thanks, yes, but if reverse a whole table expensive?>>>> On Tue, Jan 5, 2016 at 4:38 PM, Kim Tang wrote:>>>>>> Something like this?>>>>>>>>>>>> select by xxx from reverse table>>>>>>>>>>>>>>>>>> Kim>>>>>>>>>>>> Von: personal...@googlegroups.com [mailto:personal...@googlegroups.com]>>> Im Auftrag von Carfield Yim>>> Gesendet: Dienstag, 5. Januar 2016 01:55>>> An: Kdb+ Personal Developers>>> Betreff: [personal kdb+] Get first row from a group>>>>>>>>>>>> Hi, if I like to select the last row from a group, I can just do>>>>>> select by xxx from table>>>>>> if there similar syntax to select the first row? Other than using "first">>> for all elements?>>>>>> -->>> You received this message because you are subscribed to the Google Groups>>> "Kdb+ Personal Developers" group.>>> To unsubscribe from this group and stop receiving emails from it, send an>>> email to personal-kdbpl...@googlegroups.com.>>> To post to this group, send email to personal...@googlegroups.com.>>> Visit this group at https://groups.google.com/group/personal-kdbplus.>>> For more options, visit https://groups.google.com/d/optout.>>>>>> -->>> You received this message because you are subscribed to the Google Groups>>> "Kdb+ Personal Developers" group.>>> To unsubscribe from this group and stop receiving emails from it, send an>>> email to personal-kdbpl...@googlegroups.com.>>> To post to this group, send email to personal...@googlegroups.com.>>> Visit this group at https://groups.google.com/group/personal-kdbplus.>>> For more options, visit https://groups.google.com/d/optout.>>>>> --> You received this message because you are subscribed to the Google Groups> "Kdb+ Personal Developers" group.> To unsubscribe from this group and stop receiving emails from it, send an> email to personal-kdbplus+unsubscribe@googlegroups.com.> To post to this group, send email to personal-kdbplus@googlegroups.com.> Visit this group at https://groups.google.com/group/personal-kdbplus.> For more options, visit https://groups.google.com/d/optout.-- Aaron Daviesaaron.davies@gmail.com

[User]

Hi Carfield,

You can use this:

`xxx xkey table value exec first i by xxx from table

It's a little more verbose, but will avoid the need to name columns and also the performance impact of a table reverse.

Regards

Cathal Deehan

Financial Software Developer

AQUAQ Analytics

[carfield1]

Thanks a lot everyone!