ptop()
Here’s how to use ptop() function.
A.p top()
Description:
Compute the specified expression(s) over each member of a sequence, sort the result members, and return sequence numbers of the first n members in the original sequence.
Syntax:
A.ptop(n,x)
Note:
The function computes expression x,… over each member of sequence A, sorts the result members, and returns a sequence consisting of sequence numbers of the first n members in sequence A.
When n>0, sort members in ascending order; when n<0, sort members in descending order; when n=0, return null. n must not be omitted.
Parameter:
|
A |
A sequence |
|
x |
Sorting expression, which is parsed as ~ when the parameter is absent |
|
n |
An integer that should always be present; return null when the parameter is 0 |
Option:
|
@1 |
Return a single value if n is ±1 |
|
@0 |
Do not ignore null members; ignore null members by default |
|
@r |
Use standard competition ranking, where members having equal values are given equal rank and the next member is given the next highest rank |
|
@i |
Use dense ranking, where members having equal values are given equal rank and the next members are given the immediately following ranking number |
Return value:
Sequence
Example:
|
|
A |
|
|
1 |
=connect("demo").query("select top 10 NAME,DEPT,HIREDATE from employee") |
|
|
2 |
=A1.ptop(3,DEPT,age(HIREDATE)) |
Sort table sequence A1 by DEPT and age(HIREDATE) in ascending order and get a sequence consisting of sequence numbers of the first 3 members in A1; below is the result:
|
|
3 |
=A1.ptop(-2,HIREDATE) |
Sort table sequence A1 by HIREDATE in descending order and get a sequence consisting of sequence numbers of the first 2 members in A1; below is the result:
|
Use @1 option and return a single value when n is ±1:
|
|
A |
|
|
1 |
[c,e,a,f,d,b] |
|
|
2 |
=A1.ptop(1) |
Return a sequence consisting of the sequence number of the smallest member in sequence A1:
|
|
3 |
=A1.ptop@1(1) |
Use @1 option to return the sequence number of the smallest member in A1:
|
|
4 |
=A1.ptop@1(-1) |
With @1 option and as parameter n is -1, the function returns sequence number of the largest member in A1:
|
Do not ignore nulls when @0 option is used:
|
|
A |
|
|
1 |
[c,e,a,null,d,b] |
|
|
2 |
=A1.ptop(3) |
Sort members of sequence A1 in ascending order, get a sequence of sequence numbers of the first 3 members in A1 during which the null member is ignored by default, and returns result as [3,6,1] . |
|
3 |
=A1.ptop@0(3) |
Use @0 option to count the null member and return result as [4,3,6]. |
Use different ranking types to display result of ranking a sequence having duplicate values when @r/@i option is present:
|
|
A |
|
|
1 |
[a,a,b,b,b,c,c,c,d] |
Return a sequence having duplicate members. |
|
2 |
=A1.ptop(3) |
Use the dense ranking by default and get a sequence consisting of sequence numbers of the 3 smallest members in A1; the return result is [2,1,5]. |
|
3 |
=A1.ptop@r(3) |
Use standard competition ranking as @r option works – the member after the two members having the same ranking is given the next highest rank, which is equivalent to a ranking [1,1,3,3,3,6,6,8] when members are sorted; the return result is [2,1,3,4,5]. |
|
4 |
=A1.ptop@i(3) |
Use dense ranking as @i option works – members after the two members having the same ranking are given immediately following ranking numbers, which is equivalent to a ranking [1,1,2,2,2,3,3,4] when members are sorted; the return result is [2,1,5,4,3,6,7]. |
Related function: