FirstSortedValue() returns the value of the first expression that corresponds to the first value in the sort order of the second expression iterated over a number of records, as defined by a group by clause.
Syntax:
Return data type: dual
Arguments:
| Argument | Description |
|---|---|
| value Expression | The function returns the value from the field specified in value associated with the result of sorting the sort_weight field, taking into account rank, if specified. If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL. |
| sort-weight Expression | The expression containing the data to be sorted. The first (lowest) value of sort_weight is found, from which the corresponding value of the value expression is determined. If you place a minus sign in front of sort_weight, the function returns the last (highest) sorted value instead. |
| rank Expression |
By stating a rank "n" larger than 1, you get the nth sorted value. |
| distinct |
If the word DISTINCT occurs before the function arguments, duplicates resulting from the evaluation of the function arguments are disregarded. |
Examples and results:
Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in our app to see the result.
To get the same look as in the result column below, in the properties panel, under Sorting, switch from Auto to Custom, then deselect numerical and alphabetical sorting.
| Example | Result |
|---|---|
|
Temp: LOAD * inline [ Customer|Product|OrderNumber|UnitSales|CustomerID Astrida|AA|1|10|1 Astrida|AA|7|18|1 Astrida|BB|4|9|1 Astrida|CC|6|2|1 Betacab|AA|5|4|2 Betacab|BB|2|5|2 Betacab|DD Canutility|DD|3|8 Canutility|CC ] (delimiter is '|');
FirstSortedValue: LOAD Customer,FirstSortedValue(Product, -UnitSales) as MyProductWithLargestOrderByCustomer Resident Temp Group By Customer; |
MyProductWithLargestOrderByCustomer AA BB DD because AA corresponds to the largest order (value of UnitSales:18) for customer Astrida, BB corresponds to the largest order (5) for customer Betacab and DD corresponds to the largest order (8) for customer Canutility. |
|
Given that the Temp table is loaded as in the previous example: LOAD Customer,FirstSortedValue(Product, UnitSales) as MyProductWithSmallestOrderByCustomer Resident Temp Group By Customer; |
MyProductWithSmallestOrderByCustomer CC AA DD because CC corresponds to the smallest order (2) for customer Astrida, AA corresponds to the smallest order (4) for customer Betacab and DD corresponds to the smallest order (8) for customer Canutility (there is only one valid order for customer Canutility so it is both the smallest and the largest). |
|
Given that the Temp table is loaded as in first example: LOAD Customer, FirstSortedValue(Product, -UnitSales,2) as My2ndProductOrderCustomer, Resident Temp Group By Customer; |
MySecondLargetsOrderCustomer AA AA - Note! The field will only show AA once, because it is the second-largest order for both customers Astrida and Betacab. |
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!