Try including the title in the order by clause as well {order by TITLE,
TO_NUMBER(COUNT) desc}.
Using offset and limit for paging is not efficient when the table has a lot
of rows.
You try using row value constructors and ordering by the primary key column
of the table (see https://phoenix.apache.org/paged.html)On Sun, Nov 10, 2019 at 11:04 PM Lalit Jadhav <lalit.jad...@nciportal.com> wrote: > Hello, > > I'm using order by with offset for paging in apache phoenix. I am getting > a duplicate result on the next page. > > I have a view of the HBase table in Apache phoenix. I am using TO_NUMBER() > to convert my stringified column into an integer. I want to sort this > converted column in descending order. > > Example: I have an Hbase view like : > > ------------------------------ > | TITLE | COUNT | > ------------------------------ > | t8 | 10 | > ------------------------------ > | t9 | 2 | > | t4 | 1 | > | t6 | 1 | > | t10 | 1 | > | t7 | 1 | > | t43 | 0 | > | t14 | 0 | > | t11 | 0 | > | t42 | 0 | > > My Query: > > Select TITLE,COUNT from Hbase_view order by TO_NUMBER(COUNT) desc limit 5 > offset 0; > > Result : > > +------------+------------------+ > | TITLE | COUNT | > +------------+------------------+ > | t8 | 10 | > | t9 | 2 | > | t4 | 1 | > | t7 | 1 | > | t10 | 1 | > > Next Query: > > Select TITLE,COUNT from Hbase_view order by TO_NUMBER(COUNT) desc limit 5 > offset 5; > > result : > > +------------+------------------+ > | TITLE | COUNT | > +------------+------------------+ > | t7 | 1 | > | t43 | 0 | > | t14 | 0 | > | t11 | 0 | > | t42 | 0 | > +------------+------------------+ > > Here t7 is repeated on both the result. > > Expected result : > > 1st Query:- > +------------+------------------+ > | TITLE |COUNT | > +------------+------------------+ > | t8 | 10 | > | t9 | 2 | > | t4 | 1 | > | t6 | 1 | > | t10 | 1 | > > 2nd Query > >> +------------+------------------+ >> | TITLE |COUNT | >> +------------+------------------+ >> | t7 | 1 | >> | t43 | 0 | >> | t14 | 0 | >> | t11 | 0 | >> | t42 | 0 | >> +------------+------------------+ > > > Please help me to know what is the exact issue? Also, I want to know, is > CURSOR can be used for the same purpose?. > > > --- > Lalit Jadhav >
