Hi, I'm working on an editor that uses H2 as its backend. The data are accessed by page.
I'm trying to get the page to which an entry with a given ID belongs. I can calculate this if I know the row number of the entry. But it seems ROWNUM() is behaving strangely. To test it, I have a table with a unique entry that has a SEGKEY value of 55 in a small table. When I do: SELECT SEGKEY, R FROM (SELECT SEGKEY, ROWNUM() AS R FROM mytable) WHERE SEGKEY=55 I get one record back where the resulting R is 1 when I do: SELECT SEGKEY, R FROM (SELECT SEGKEY, ROWNUM() AS R FROM mytable) WHERE MOD(SEGKEY, 55)=0 I get one record back where the resulting R is 42 (the correct row number of the entry with SEGKEY=55 I've seen the documentation that states: "...The first row has the row number 1, and is calculated before ordering and grouping the result set, but after evaluating index conditions (even when the index conditions are specified in an outer query)". What does it mean exactly? How can i make the first query return the expected value for R. To me it seems very illogical that ROWNUM() behaves so differently depending on what the WHERE clause is. I would expect the inner query to give back a set of values that does not change when copied into the main result. Any hint would be appreciated. Thanks, -yves -- 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.
