cancel
Showing results for 
Search instead for 
Did you mean: 

Get first row from a group

carfield1
New Contributor
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?


7 REPLIES 7

kuentang
New Contributor

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.

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

mkrmkr
New Contributor II
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
New Contributor II

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)

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

cathal_deehan
New Contributor
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

Thanks a lot everyone!