Is ORDER BY stable?

Question

question

Mischa Spelt asked Jun 3, '20 anthony.johnson edited Jun 3, '20

Is ORDER BY stable?

Hi, I was wondering if the sort algorithm for lists is stable.

If I pull from a list without query, I get results in FIFO order. If I sort the list, e.g. ORDER BY [Priority], are all results with the same priority still returned in list push order?

We noticed that for table queries this is no longer the case, from the release notes:

Because of performance improvements to query(), the row order is not guaranteed unless using an ORDER BY statement

Side question: does any ORDER BY force the results to have row order as secondary layer, or does it need to be ORDER BY ROW_NUMBER, specifically ?

Software Version:

FlexSim 20.0.6

query lists list pull flexsim 20.0.6 order by

Answer 1 · 2020-06-03T16:24:04Z

anthony.johnson answered Jun 3, '20 anthony.johnson edited Jun 3, '20

Yes, ORDER BY is stable. If you use an ORDER BY, then ties will be ordered in the same order as they were pushed onto the list.

That said, even if you don't have an ORDER BY, the query result will still be in push order. The only time when ordering is not "guaranteed" unless you use an ORDER BY is if you are doing a join or if you are looking up by an indexed column. For speed purposes, we index values for the right-side table of the join, and some table types support indexing (for example bundle tables where you've defined a column as indexed). These indexes are not necessarily ordered by the source row. Thus, in these scenarios, you would want to use an ORDER BY. However, lists (at least for now) do not support indexing of columns. So if you're doing a simple lookup with no WHERE filter, then the result will always be in push order. Further, even if you have a WHERE filter, the result will still be in push order now. However, if at some point in the future we add indexing of list fields, and you decide in a future version of your model to check the "Indexed" box, then results may not be in row order in that future hypothetical.

And, yes, any ORDER BY will inherently order by row number on ties. That is, if the table is structured like an actual table. It gets a little weird for tables that aren't actually tables but rather traversal algorithms, like storage system slots, items, and a future "Objects()" table that I'm working on now.

· 2

anthony.johnson ♦♦ commented · Jun 03, 2020 at 04:27 PM

@Jordan Johnson Please correct me if I'm wrong on that second point.

0 ·

Jordan Johnson ♦♦ anthony.johnson ♦♦ commented · Jun 03, 2020 at 04:41 PM

If you don't have an ORDER BY statement, and you have any index in the query, then those results likely won't be ordered by row. But the list doesn't have any indexed fields, so it's not an issue there.

0 ·

question