Hello! Try the following query: SELECT RANK() OVER (ORDER BY start_date DESC NULLS FIRST) R, * FROM test ORDER BY 1; Some rows have the same rank: 149 130 2019-01-11 00:00:00-05 150 217 2019-01-02 00:00:00-05 150 218 2019-01-02 00:00:00-05 152 164 2019-01-01 00:00:00-05 152 166 2019-01-01 00:00:00-05 154 165 2018-11-16 00:00:00-05 That means rows with ID 217 and 218 may be returned every time in any order. It isn't a bug, it is a valid standard-compliant behavior.
Offset pagination a bad thing, but if you really need to use it, there are two common solutions: 1. You can add a unique non-null column to the ORDER BY clause, such as primary key column(s): SELECT * FROM test ORDER BY start_date DESC NULLS FIRST, id OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY; 2. You can use standard WITH TIES clause: SELECT * FROM test ORDER BY start_date DESC NULLS FIRST OFFSET 100 ROWS FETCH NEXT 50 ROWS WITH TIES; This query returns 51 rows, because after 50th row there is an additional row with the same rank. These possible additional returned rows inform your application about this situation and it can handle it somehow: 217 2019-01-02 00:00:00-05 218 2019-01-02 00:00:00-05 For example, it can process all of them and run next query with OFFSET 151 ROWS FETCH NEXT 50 ROWS WITH TIES. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/9a785bc4-7faa-4a70-8e03-f37c4bf76c63n%40googlegroups.com.
