phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas D'Silva" <twdsi...@gmail.com>
Subject Re: Apache phoenix problem with order by and offset giving duplicate results in paging
Date Wed, 13 Nov 2019 00:27:35 GMT
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.jadhav@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
>

Mime
View raw message