SQL Sequence Search_First and Search_Last Functions

The seq_search_first() and seq_search_last()functions combine the seq_search() and seq_first() or seq_last() to produce a sequence ordered according to the input sequence, starting with the value specified in the second argument and having the maximum number of values specified in the third argument.

Following is an example script demonstrating the seq_search_first() and seq_search_last()functions:

 
    SELECT symbol, seq_search_first(day, 20130101, 3) as "Q1_first3_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol
    Q1_first3_13{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101, 20130104, 20130106}
     
    SELECT symbol, seq_search_last(day, 20130301, 3) as "Q1_last3_13"
    FROM Quote WHERE symbol='SYM0';
     
    symbol
    Q1_last3_13{}
    ------------------------------------------------------------------------------
    SYM0
    {20130101}
     

In the example above, the “date” values 20130101 and 20130301 (integer values in format yyyymmdd) are used to specify the from and till dates for the sequence of day values in the first quarter of year 2013.

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