Support Questions
Find answers, ask questions, and share your expertise

Alternate to 'OFFSET' in Phoenix4.4

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


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).

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.