[SOLVED] Force ordering in USE KEYS statement

SQL++
1

Why the USE KEYS statement doesn’t respect the order of his argument, assuming the argument is an array?

More precisely, if I have the following query:
SELECT * FROM default USE KEYS [“id1”, “id2”]

And I call it many times, the order of the result set doesn’t remain the same in every query.

How can I force ordering in USE KEYS?

2

Hi,

You should use ORDER BY to ensure ordering of results. Internally, the query engine does a lot of parallel processing, so it does not guarantee ordering.

Gerald

3

Thanks geraldss, but I have no order criteria for this.
Can you tell me if there’s an ORDER BY clause that let me order a result set depending on elements in an array?
Example: order by id in [“id1”, “id2”]

4

[SOLVED]
ORDER BY ARRAY_POSITION(key, id)

Thank you for the hint!

5

Nifty!

…

6

Is it sarcastic?
Are there more elegant workarounds?

7

No, of course not sarcastic. It is nifty.

Thanks,
Gerald

8

Ok, thank you so much!

9

Here is another option :

SELECT *, meta(DEFAULT).id id FROM default USE KEYS [“id1”, “id2”] ORDER BY id;

meta() function returns the meta-data values of the keys and meta(DEFAULT).id will just return the actual id of the key which you can use to sort the output.

Cheers,
Manik

10

Is the above query supposed to order by the lexicographic order of the ids? Because I want to order the output by the position of the id in the array.
For example, If I have the following array of keys = [“c”, “a”]
I want the output [“obj with key = c”, “obj with key = a”]
And not the set sorted by id

11

Correct.

ORDER BY ARRAY_POSITION()

sorts by input order, as you indicated.

ORDER BY id

sorts by id value and not by input order in the input array.

1 Like
12

I am using this query to do order on basis of an array [ ];
Query:
select raw t[].unique_id from Test t where meta(t).id=“order_array”;
o/p ->[
[
“2”,
“7”,
“4”,
“5”,
“6”,
“3”
]
]
Now, using this above query in below (order by array_position())
Query:
select s.language,
s.physicalIndex
FROM Test AS s
let ordered=(select raw t[].unique_id from Test t where meta(t).id=“order_array”)
order by array_position(ordered,s.physicalIndex);

But here I am not getting the ordered output as expected on basis of array position.
As per requirement, I can not hardcoded the array here.
Please help.

13

The output of subquery o/p is array of arrays [[…]]
Try this.
let ordered=(select raw t[].unique_id from Test t where meta(t).id=“order_array”)[0]

14

Ok, thank you so much ! have used it in order by clause and it worked…