Are there plans to support new window functions?

1

Hi.

Are there plans to support new window functions?

For example, Nth value functions.

  • FIRST_VALUE
  • LAST_VALUE
  • NTH_VALUE
2

@balaji.ramaswamy Please have a look.If I want to expand myself, what should I do?

3

It seems that “first_value” and “last_value” functions are available, but they are not mentioned in the documentation.
Most importantly, there is no “nth_value” function

4

@bigfacewo Sorry about the delay, I was OOO. Let me see if we can add it to the docs and f there is an enhancement request for NTH_VALUE

5

@balaji.ramaswamy Can you confirm whether the “nth_value” function is supported? When I use only one parameter, I will be prompted that the parameter is wrong. When I complete the parameters (two parameters), I will be prompted that I cannot find a matching function.

6

@bigfacewo It seems like NTH_VALUE is supported, can you send the profile so we can see the syntax?

7

@balaji.ramaswamy

image
image1549×521 22.3 KB

here is the profile:
d0a3e990-cd1a-454d-a1be-4feae79c17af.zip (11.4 KB)

8

Thanks for the profile @bigfacewo NTH value does not seem to be there, let me check internally and get back to you

9

@balaji.ramaswamy Any update?

10

@bigfacewo There was a ticket put to fix the docs so we can follow the right syntax, but I am getting failure finding function, so I have checked internally on the ticket

11

Did you find any way to do the Nth_Value ?

12

@Maziha Something like this?

To find 3rd highest salary

select * from(
select emp_name, emp_salary, dense_rank() 
over(order by emp_salary desc)r from Emp) 
where r=3;
13

Is there an update on the ‘nth_value’ function? @balaji.ramaswamy

1 Like
14

@bigfacewo The functionality is still pending to be implemented

15

May I ask if there are any plans to support the “NTH_VALUE” function now? This function is very important.

@balaji.ramaswamy

16

@bigfacewo Not yet, I see the function is still not implemented