# functional form **URL:** \ **Category:** Community Support\ **Tags:** kdb-and-q\ **Created:** [September 24, 2010, 12:30pm UTC](https://forum.kx.com/t/functional-form/6726 "2010-09-24T12:30:00Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Jyoti](https://avatars.discourse-cdn.com/v4/letter/j/6f9a4e/32.png) [@Jyoti](https://forum.kx.com/u/Jyoti)\ **Post date:** [September 24, 2010, 12:30pm UTC](https://forum.kx.com/t/functional-form/6726/1 "2010-09-24T12:30:00Z")
Hi All,could anybody help me out to get a solution for the below issue:Req: I need to get the ids from the below table where values are inincreasing order (by id). (2009.05.01 value should be \> next datesvalue ((2009.06.01 \> 2009.05.01) and (2009.07.01 \> 2009.06.01) and(2009.08.01 \> 2009.07.01))Example:Table: tid period val--------------------------------1001 2009.05.01 50501001 2009.06.01 60001001 2009.07.01 70001001 2009.08.01 80001002 2009.05.01 55501002 2009.06.01 50001002 2009.07.01 55001002 2009.08.01 60501003 2009.05.01 98761003 2009.06.01 88761003 2009.07.01 78761003 2009.08.01 6876from the above rows the valid record is:id period val--------------------1001 2009.08.01 8000Thanks in advance,Jyoti ---
**Author:** ![Wei](https://avatars.discourse-cdn.com/v4/letter/w/90ced4/32.png) [@Wei](https://forum.kx.com/u/Wei)\ **Post date:** [September 24, 2010, 4:18pm UTC](https://forum.kx.com/t/functional-form/6726/2 "2010-09-24T16:18:00Z")
Hi Jyoti, Hope this helps: select id from (`id xgroup `period xasc t) where 0 Cheers, 2010/9/24 Jyoti \: \> Hi All, \> \> could anybody help me out to get a solution for the below issue: \> \> \> Req: I need to get the ids from the below table where values are in \> increasing order (by id). (2009.05.01 value should be \> next dates \> value ((2009.06.01 \> 2009.05.01) and (2009.07.01 \> 2009.06.01) and \> (2009.08.01 \> 2009.07.01)) \> \> Example: \> Table: t \> id =A0 period =A0 =A0 =A0 =A0 val \> -------------------------------- \> 1001 2009.05.01 5050 \> 1001 2009.06.01 6000 \> 1001 2009.07.01 7000 \> 1001 2009.08.01 8000 \> 1002 2009.05.01 5550 \> 1002 2009.06.01 5000 \> 1002 2009.07.01 5500 \> 1002 2009.08.01 6050 \> 1003 2009.05.01 9876 \> 1003 2009.06.01 8876 \> 1003 2009.07.01 7876 \> 1003 2009.08.01 6876 \> \> from the above rows the valid record is: \> \> id =A0 period =A0 =A0 val \> -------------------- \> 1001 2009.08.01 8000 \> \> Thanks in advance, \> Jyoti \> \> – \> Submitted via Google Groups\ ---
**Author:** ![Aaron\_Davies](https://avatars.discourse-cdn.com/v4/letter/a/8e7dd6/32.png) [@Aaron\_Davies](https://forum.kx.com/u/Aaron_Davies)\ **Post date:** [September 24, 2010, 6:41pm UTC](https://forum.kx.com/t/functional-form/6726/3 "2010-09-24T18:41:00Z")
On Sep 24, 7:30?am, Jyoti \ wrote:\> Req: I need to get the ids from the below table where values are in\> increasing order (by id). (2009.05.01 value should be \> next dates\> value ((2009.06.01 \> 2009.05.01) and (2009.07.01 \> 2009.06.01) and\> (2009.08.01 \> 2009.07.01))\>\> Example:\> Table: t\> id ? period ? ? ? ? val\> --------------------------------\> 1001 2009.05.01 5050\> 1001 2009.06.01 6000\> 1001 2009.07.01 7000\> 1001 2009.08.01 8000\> 1002 2009.05.01 5550\> 1002 2009.06.01 5000\> 1002 2009.07.01 5500\> 1002 2009.08.01 6050\> 1003 2009.05.01 9876\> 1003 2009.06.01 8876\> 1003 2009.07.01 7876\> 1003 2009.08.01 6876\>\> from the above rows the valid record is:\>\> id ? period ? ? val\> --------------------\> 1001 2009.08.01 8000q)0!last each’select from(`id xgroup t)where val~'asc each valid period val--------------------1001 2009.08.01 8000–Aaron [Daviesaaron.davies@gmail.com](mailto:Daviesaaron.davies@gmail.com)\ ---
**Author:** ![Jyoti](https://avatars.discourse-cdn.com/v4/letter/j/6f9a4e/32.png) [@Jyoti](https://forum.kx.com/u/Jyoti)\ **Post date:** [September 27, 2010, 1:56pm UTC](https://forum.kx.com/t/functional-form/6726/4 "2010-09-27T13:56:00Z")
Thanks guys and sorry for late reply, Actually none of solutions worked 100% for my requirement. Lets assume my table is having the below rows: q)meta t | c???? | t f a | | --- | --- | | id??? | i | | period | d | | val?? | f | ## q)select id,period,val from t id???? period???????? val 1001 2010.06.30 1000 1001 2010.07.31 1100 1001 2010.08.31 1400 1001 2010.09.30 1500 1002 2010.06.30 2341 1002 2010.07.31 5341 1002 2010.08.31 3341 1002 2010.09.30 5679 1003 2010.06.30 8765 1003 2010.07.31 7765 1003 2010.08.31 6213 1003 2010.09.30 513 1004 2010.06.30 1763 1004 2010.07.31 8763 1004 2010.08.31 8763 1004 2010.09.30 9109 1005 2010.06.30 1005 2010.07.31 1119 1005 2010.08.31 1509 1005 2010.09.30 2509 1006 2010.06.30 1006 2010.07.31 1006 2010.08.31 1234 1006 2010.09.30 1123 ## Valid results are: id????? period??????? val 1001 2010.09.30 1500 1005 2010.09.30 2509 Solutions: 1. select id from (`id xgroup `period xasc t) where 0\Empty response 1. ## q)0!last each'select from(`id xgroup t)where val~'asc each val id?? period???? val 1001 2010.09.30 1500 1004 2010.09.30 9109 ===\> Invalid record 1005 2010.09.30 2509 Not working for all requiremnets (where all next values are same as pervious) 1. Need to Check: select rowid,period,val from (select av:all(deltas val)\>0, max period,max val by rowid from `period xasc t) where av=1 Response: id?? period???? val * * * 1001 2010.09.30 1500 need id=1005 too please let me know if the requirement is still not clear Thanks, Jyoti ---
**Author:** ![Attila](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@Attila](https://forum.kx.com/u/Attila)\ **Post date:** [September 27, 2010, 2:17pm UTC](https://forum.kx.com/t/functional-form/6726/5 "2010-09-27T14:17:00Z")
q)select by id from t where(all(\>':)0^;val)fby id? | id? | period??? val | | --- | --- | | 1001 | 2010.09.30 1500 | | 1005 | 2010.09.30 2509 | | ? Attila | | ---
**Author:** ![aka\_magnus](https://avatars.discourse-cdn.com/v4/letter/a/b19c9b/32.png) [@aka\_magnus](https://forum.kx.com/u/aka_magnus)\ **Post date:** [September 27, 2010, 2:27pm UTC](https://forum.kx.com/t/functional-form/6726/6 "2010-09-27T14:27:00Z")
## 0!last each'select from (`id xgroup`id`period xasc t) where all each 0id period val 1001 2010.09.30 1500 1005 2010.09.30 2509 ---
**Author:** ![Jyoti](https://avatars.discourse-cdn.com/v4/letter/j/6f9a4e/32.png) [@Jyoti](https://forum.kx.com/u/Jyoti)\ **Post date:** [September 27, 2010, 2:37pm UTC](https://forum.kx.com/t/functional-form/6726/7 "2010-09-27T14:37:00Z")
Thanks Attila… working fine ---
**Author:** ![Bishop\_Cotton](https://avatars.discourse-cdn.com/v4/letter/b/dfb087/32.png) [@Bishop\_Cotton](https://forum.kx.com/u/Bishop_Cotton)\ **Post date:** [October 1, 2010, 12:26am UTC](https://forum.kx.com/t/functional-form/6726/8 "2010-10-01T00:26:00Z")
Attilla and Aaron were gracious to respond, but it would have been preferable? if you had? at least made an attempt to solve your problem? - like? reading the manual - , instead of looking for someone on the list to do the thinking for you. BC ---
**Author:** ![Jyoti](https://avatars.discourse-cdn.com/v4/letter/j/6f9a4e/32.png) [@Jyoti](https://forum.kx.com/u/Jyoti)\ **Post date:** [October 1, 2010, 8:05am UTC](https://forum.kx.com/t/functional-form/6726/9 "2010-10-01T08:05:00Z")
Hi Bishop, I agree. Actually I tried few attempts but failed to meet the requirement, I have posted this as I was running out of time. In future, I’d try to fix it at my own. Thanks,