cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieve maximum lesser than x from table.

Success_aman
New Contributor
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
2 REPLIES 2

ikorkhov
New Contributor
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      100
2  10      200
1  10      300
2  30      400
3  40      500
1  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      200
1  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      100
1  10      300
2  10      200


HTH


alvi_kabir919
New Contributor
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)t
id ver data
-----------
1  2   14
2  2   53
1  1   63
2  1   13
3  1   85
1  3   92
q)f:{`id`ver xasc select from t where ver in asc[ver] raze -1 0 +/: asc[ver]?x}
q)f 3
id ver data
-----------
1  2   14
1  3   92
2  2   53
You can pass in multiple inputs as well:
q)f 3 2
id ver data
-----------
1  1   63
1  2   14
1  3   92
2  1   13
2  2   53
3  1   85

Sincerely,

Alvi