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?
Something like this?<o:p></o:p>
<o:p> </o:p>
select by xxx from reverse table<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Kim<o:p></o:p>
<o:p> </o:p>
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<o:p></o:p>
<o:p> </o:p>
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?
<o:p></o:p>
–
Submitted via Google Groups
Thanks, yes, but if reverse a whole table expensive?
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
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:abcdef;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]!enlista;(cols[t]excepta)!(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)
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)
Thanks a lot everyone!
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:abcdef;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]!enlista;(cols[t]excepta)!(first,/:cols[t]excepta)]> 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 <kuen…> 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?>>>>>> –>>>
Submitted via Google Groups</kuen…>