sortCol

sortCol(expr, [asc=true], [nullsLast])

Generates metacode that specifies the null sorting rule. This function is typically used with the sql function. Null sorting specifies where null values appear in the result set when data is sorted in ascending or descending order: at the beginning or at the end.

expr is of STRING or CODE type, and can be a scalar or vector.

• If expr is of STRING type, it specifies the name of the sort column, such as “value”.
• If expr is of CODE type, it specifies a column calculation expression, such as <price + volume>. The query sorts the data by the calculation result.

asc is a BOOL scalar or vector that specifies the sort direction for each sort item in expr. true indicates ascending order, and false indicates descending order. The default value is true.

nullsLast is a BOOL scalar or vector that specifies whether to place null values at the end when sorting. When set to true, null values are placed at the end (nulls last); when set to false, they are placed at the beginning (nulls first). The default value is null, which is equivalent to specifying false.

A CODE scalar or vector that indicates the null sorting rule. The return value can be passed directly to the csort and orderBy parameters of the sql function.

Example 1: Specify a single sort column and place null values at the end in an SQL query.

// Create a table in which the value column contains null values
t = table(1..5  as id, 5 8 NULL 2 NULL as value)

// Sort the data in ascending order by the value column, and place rows with null values in the value column at the end
sql(sqlCol("*"), t, orderBy=sortCol("value", true, true)).eval()

Example 2: Specify multiple sort columns in an SQL query, placing null values in one column at the end and null values in another column at the beginning.

// Create a table in which both the price and seq columns contain null values
t = table(
    [1, 2, 3, 4, 5] as id,
    [10, NULL, 10, 20, NULL] as price,
    [NULL, 2, 1, 3, 4] as seq
)

/* price: sort in descending order and place null values at the end
seq: sort in ascending order and place null values at the beginning
*/
sql(sqlCol("*"), t, 
    orderBy=sortCol([`price, `seq], 
    [false, true], 
    [true, false])).eval()

Example 3: In the sql function, pass the return value of sortCol to the csort parameter, and use it with a limit clause to sort data within each group.

// Create a table in which the seq column contains null values within each sym group
t = table(
    [`A, `A, `A, `B, `B, `B] as sym,
    [3, NULL, 1, NULL, 2, 1] as seq,
    [30, 20, 10, 50, 40, 60] as value
)

// Generate the sort rule used by csort: sort by seq in ascending order and place null values at the end
sortRule = sortCol("seq", true, true)

// Within each sym group, sort by sortRule and take the first 2 records, excluding records that contain null values
sql(select=sqlCol("*"),
    from=t,
    groupBy=sqlCol("sym"),
    groupFlag=0,
    csort=sortRule,
    limit=2).eval()

Related functions: sql, sqlCol, eval