The only(!) way to get a deterministic ordering from a relational database is to use ORDER BY.
Just because you get a consistent ordering between two different runs (in some databases), does not mean you get it on a third run. As soon as you insert or update or even change the WHERE condition, the DBMS is free to return the rows in any order. So, if you want a deterministic order on your rows you have to use ORDER BY. There is no other way. On Feb 9, 8:38 am, Remo <[email protected]> wrote: > Eric, > > I completely agree that the order is not defined in my case. But in > accordance with other DBMS (HSQLDB, MySql) I ran the query, I expect > the order to be deterministic. > I tried to demonstrate the effect with a small test case, but did not > succeed, as it always worked as expected. So I still assume that a bug > causes this behavior in some rare cases. > > Regards, > Remo > > On Feb 8, 6:11 pm, Eric Faulhaber <[email protected]> wrote: > > > Remo, > > > If all of the records in the table have the same values for the > > columns in your ORDER BY clause, you effectively are not telling the > > database how you want your results to be sorted, so the order in which > > you get your results back is undefined. There is no guarantee or > > requirement that results will come back in the same order every time > > you execute the same query, beyond what you specify in your ORDER BY > > clause. > > > If you expect to see your results in the same order every time you run > > your query, you will need to sort explicitly on some unique constraint > > as the least significant criterion in your ORDER BY clause. If your > > table has a primary key, that's a good candidate. The resulting sort > > order may not have business meaning, but it will be stable as long as > > records are not added, changed, or deleted from the table between > > query runs. > > > Regards, > > Eric Faulhaber > > > On Feb 8, 11:13 am, Remo <[email protected]> wrote: > > > > I encountered a query where the sort order is correct, but not stable, > > > i.e. not every run of the query returns the rows in the same order. > > > Unfortunately, I was not able to create a simple test case. The query > > > is: > > > > SELECT > > > NOON_REPORT_FIELD_INST.INSTALLATION_ID, > > > NOON_REPORT_FIELD_INST.NOON_REPORT_FIELD_ID, > > > NOON_REPORT_FIELD.SORT_POSITION > > > FROM NOON_REPORT_FIELD_INST > > > JOIN NOON_REPORT_FIELD ON NOON_REPORT_FIELD_INST.NOON_REPORT_FIELD_ID > > > = NOON_REPORT_FIELD.NOON_REPORT_FIELD_ID > > > ORDER BY NOON_REPORT_FIELD_INST.INSTALLATION_ID, > > > NOON_REPORT_FIELD.SORT_POSITION > > > > It might help to know, that INSTALLATION_ID is the same for all rows > > > and SORT_POSITION is NULL for all rows. > > > I will provide the DB with data if necessary for further analysis. > > > > Thanks, > > > Remo -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
