I have different verisons of a ID in a table , I want to retrieve the rows having data of the input version and data of its previous maximum version from a table. I tried following but it gives me wrong data as there may be versions greater than x. Select from tab where version>=exec max version from tab where version<x.></x.>
It’s not quite clear from your question what exactly you need to retrieve. Do you want to select all rows matching the input and its previous maximum regardless of their IDs or do you want to select rows matching maximum version *per id*?
q)show tab:([]id:1 2 1 2 3 1;version:20 10 10 30 40 30;data:100 200 300 400 500 600)id version data---------------1 20 1002 10 2001 10 3002 30 4003 40 5001 30 600
If the former, this should work:
q){select from tab where version in exec 2 sublist distinct desc version from tab where version < x}[20]id version data---------------2 10 2001 10 300
If the latter, a slightly more complex query should do:
q){(ungroup select 2 sublist desc version by id from tab where version < x) ij 2!tab}[30]id version data---------------1 20 1001 10 3002 10 200
HTH
For getting rows with input version and version before it you can do:
q)t:([]id:1 2 1 2 3 1;ver:2 2 1 1 1 3;data:14 53 63 13 85 92)q)tid ver data-----------1 2 142 2 531 1 632 1 133 1 851 3 92q)f:{idver xasc select from t where ver in asc[ver] raze -1 0 +/: asc[ver]?x}q)f 3id ver data-----------1 2 141 3 922 2 53
You can pass in multiple inputs as well:
q)f 3 2id ver data-----------1 1 631 2 141 3 922 1 132 2 533 1 85
Sincerely,
Alvi