I want to sort an array inside the table in assending order. Can someone help with SQL query for that. This is the table structure and Path column is the Array.
SQL Help
FlexSim 22.1.4
Hi @sachin T3, was one of Joerg Vogel's or Jason Lightfoot's answers helpful? If so, please click the "Accept" button at the bottom of the one that best answers your question. Or if you still have questions, add a comment and we'll continue the conversation.
If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept and comment back to reopen your question.
You can sort the table based on values in the array but not reorder the arrays stored in Path in via SQL in one query. So:
If those arrays were constructed from another table with SQL then you could sort it as part of the ARRAY_AGG grouping with and ORDER BY clause.
if I create a tempArray a local Array and then sort it ascending and then i can store the sorted values in the table.
Can you please help with the sorting query in the Array. Also will it change the sequence based on that. this query isnt working.
Hello @sachin T3,
Using @Jason Lightfoot model I made the following script that takes the array from each cell in the table and sort the array in ascending order.
This is done by pulling the array from the table into a array variable.
The array variable is first queried and sorted in ascending order and then the table that results from that query is queried again to aggregate into an array.
The results are then copied back into the cell of the original array.
Array TempArray;
for(int i = 1; i <= Table("GlobalTable1").numRows; i++){
TempArray = Table("GlobalTable1")[i]["Path"];
Table results = Table.query("SELECT ARRAY_AGG(number) FROM (SELECT $2 AS number FROM $1 ORDER BY number ASC)", TempArray.length, TempArray[$iter(1)]);
Table("GlobalTable1")[i]["Path"] = results[1][1];
}
You can query an array. Then you can order the result table. This table can be aggregated in another query to get an array back. You can probably do this over arrays in table tows by join and separate it in an ordered structure of a ROW_NUMBER clause. But my experience on this reaches not far enough to build this code structure. I hope you find someone who can do it.
14 People are following this question.
