Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Alternate to 'OFFSET' in Phoenix4.4

Alternate to 'OFFSET' in Phoenix4.4

New Contributor

I want to write a pagination query in phoenix.

I can see OFFSET option on phoenix web-site but I think it is available in latest phoenix4.8 version.

I am using Phoenix 4.4 HDP 2.3.2

Is there any alternative to skip records while selecting like OFFSET does?

@Josh Elser

2 REPLIES 2

Re: Alternate to 'OFFSET' in Phoenix4.4

In Phoenix 4.4,

You may use RVC for pagination but it will be helpfull in certain cases only.(like if you are scanning on the primary axis).

https://phoenix.apache.org/paged.html

Other option is to use NTH_VALUE function, (I have seen one of our customer using it for something similar). May not be optimal in performance always.

And (if you have multiple columns you can concatenate them like this too).

NTH_VALUE( col1||col2||col3, 2 ) WITHIN GROUP (ORDER BY col2||col3 desc) 
NTH_VALUE(col1||col2||col3, 2 ) WITHIN GROUP (ORDER BY col1||col2||col3 desc) 

Re: Alternate to 'OFFSET' in Phoenix4.4

JFYI, OFFSET feature is shipped with HDP 2.5.

Don't have an account?
Coming from Hortonworks? Activate your account here