H2 limit and offset, is there a guarantee on ordering?

When using LIMIT and OFFSET, what are the guarantees that each such statement will order the input in the same way?
To illustrate:

If:

SELECT FROM toys ORDER BY price ASC LIMIT 3;


returns:

Can i be sure the next query:

SELECT FROM toys ORDER BY price ASC LIMIT 3 OFFSET 3;


returns:

After all, all three balls have the same price, so any ordering of the three rows is valid.

You should include a second key in the order by so the sort is stable (that is, the keys have no ties).
– Gordon Linoff
Jun 29 at 16:48

order by

@Gordon, so it's up to me to construct the 'order by' so that ordering is defined? I can add an index at creation time for this.
– Ivana
Jun 29 at 17:09

1 Answer
1

If you want the rows in a particular order, just specify it. Maybe:

SELECT FROM toys ORDER BY price, product LIMIT 3;


Per SQL definition the database will return the rows in no specific order when no ORDER BY is specified. The database is free to return the rows as it pleases. The order of these rows may change in time as well.

ORDER BY

If an ORDER BY is present, the database is free to return the equivalent rows (per the ordering criteria) in any order, and also this order may change over time.

ORDER BY

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.