top()

A.top(n,x) 

Compute expression x over each member of the sequence A, sort the resulting members, and return a sequence consisting of the first n values of x.

A.top(n;x,...)

Compute expression x,… over each member of the sequence A, sort the resulting members, and return a sequence consisting of the first n members of sequence A.

A.top(n,y,x)

Compute expression x over each member of the sequence A and then expression y over results of computing expression x, sort values of x according to the values of y, and return a sequence consisting of the first n values of x.

A

A sequence.

n

An integer.

y

An expression.

x

An expression.

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

A

1

=connect("demo").query("select top 10 NAME,SALARY from employee")

Return a table sequence:

2

=A1.top(3,SALARY+500)

Compute expression SALARY+500 over each record of A1’s table sequence, sort the table sequence by the expression’s values, and return a sequence consisting of the first 3 smallest values of the expression:

3

=A1.top(-3,SALARY)

Sort A1’s table sequence by SALARY, and, as n<0, return a sequence consisting of the first 3 largest SALARY values:

4

=[2,6,4,12,6,5,2].top(4)

As parameter x is absent, the function returns the following result:

A

1

=connect("demo").query("select top 10 NAME,SALARY from employee")

Return a table sequence:

2

=A1.top(3;SALARY+500)

Compute expression SALARY+500 over each record of A1’s table sequence, sort the table sequence by the expression’s values, and return a sequence consisting of A1’s records corresponding to the first 3 smallest values of the expression:

3

=A1.top(-6;DEPT,SALARY)

Sort A1’s table sequence by DEPT and SALARY, and return a sequence consisting of A1’s records corresponding to the first 6 largest values of the two fields:

A

1

[21,3,12,5]

2

=A1.top(3,~%10,~+6)

First, compute expression x ~+6 over each member of A1’s sequence and get [27,9,18,11] as the expression’s result; then compute expression y ~%10 over x’s result and get [7,9,8,1] as y’s result; and finally return a sequence consisting of values of x corresponding to the first 3 smallest values of y; below is the result sequence:

3

=A1.top(-3,~%10,~+6)

As parameter n is -3, the function returns a sequence consisting of values of x corresponding to the first 3 largest values of y; below is the result sequence:

A

1

=[2,6,4,12,6,5]

2

=A1.top(1)

Return a sequence made up of the one smallest value:

3

=A1.top@1(1)

With @1 option and as parameter n is 1, the function returns the smallest value among members of A1’s sequence:

4

=A1.top@1(-1)

With @1 option and as parameter n is -1, the function returns the largest value among members of A1’s sequence:

A

1

=[5,7,2,null,3]

2

=A1.top(3)

Get the first 3 smallest member values in the sequence, during which the null member is by default ignored:

3

=A1.top@0(3)

As @0 option is present, the null member is also counted:

A

1

[a,b,e,b,d,a,c,c,b]

Return a sequence having duplicate members.

2

=A1.top(3)

Use dense ranking by default and get the first 3 smallest values:

3

=A1.top@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,9] when members are sorted; below is the return result:

4

=A1.top@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,5] when members are sorted; below is the return result: