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.
