• esProc
Tutorial
Function Reference
Code Reference
User Reference
External Library Guide
Data File Tool Manual
DQL Tutorial
Cluster Server Manager Manual
SPL WIN Manual
• YModel
User Reference
JSON-style Parameter Guide
• ReportLite
User Reference
• Official Website

# 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: