SQL Sequence TopMax/TopMin TopPosMax/TopPosMin Functions

The seq_top_max() function returns the greatest N values (3 in this example) from the input sequence. The seq_top_min() function returns the smallest N values. The seq_top_pos_max() function returns the positions of the greatest N values in the input sequence. The seq_top_pos_min() function returns the positions of the smallest N values in the input sequence.

Following is an example script demonstrating these functions:

 
    create table SimpleSequence( testNumber unsigned(4) primary key,
    dVal1 sequence(double), dVal2 sequence(double) );
    INSERT INTO SimpleSequence(testNumber,dVal1,dVal2)
    VALUES(1,[1,2,3,4,5,6,7,8,9], [1,2,3,4,5,6,7,8,9]);
     
    SELECT dVal1, dVal2, seq_top_max(dVal1,3) as "top_max",
        seq_top_pos_max(dVal1,3) as "top_pos_max",
        seq_top_min(dVal2,3) as "top_min",
        seq_top_pos_min(dVal2,3) as "top_pos_min"
    FROM SimpleSequence WHERE testNumber=1;
     
    dVal1{}
    dVal2{}
    top_max{}
    top_pos_max{}
    top_min{}
    top_pos_min{}
    ------------------------------------------------------------------------------
    {1.000000, 2.000000, 3.000000, 4.000000, 5.000000, 6.000000, 7.000000,
      8.000000, 9.000000}
    {1.000000, 2.000000, 3.000000, 4.000000, 5.000000, 6.000000, 7.000000,
      8.000000, 9.000000}
    {9.000000, 8.000000, 7.000000}
    {8, 7, 6}
    {1.000000, 2.000000, 3.000000}
    {0, 1, 2}
     

Sample script

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    g 11