ptop()

Read(2275) Label: sort, ordinal number,

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 ordinal 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 ordinal 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 ordinal 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 ordinal 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 ordinal number of the smallest member in sequence A1:

3

=A1.ptop@1(1)

Use @1 option to return the ordinal number of the smallest member in A1:

4

=A1.ptop@1(-1)

With @1 option and as parameter n is -1, the function returns ordinal 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 ordinal 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 ordinal 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:

A.pos()

A.sort()

A.pos(x)

A.psort()

A.top()