Sort and Other Functions

YQL includes built-in functions such as sort, which are appended to the SELECT statement with the pipe symbol ("|"). These functions are applied to the result set after the SELECT statement performs all other operations, such as applying filters and limits.

In the following SELECT statement, the sub-select returns a list of GUIDs, and the outer select returns a set of profiles, one for each GUID. This set of profiles is piped to the sort function, which orders the results according to the value of the nickname field.

select * from social.profile where guid in (select guid from social.connections where owner_guid=me) | sort(field="nickname")

Multiple functions can be chained together with the pipe symbol ("|"). The following statement queries the local.search table for restaurants serving pizza. The results are piped to the sort function, then to the reverse function. The final result contains 20 rows, sorted by rating from high to low.

select Title, Rating.AverageRating from local.search(20) where query="pizza" and city="New York" and state="NY" | sort(field="Rating.AverageRating") | reverse()

Results can also be sorted with multiple criteria. The sort function can take multiple fields with optional values specifying the sort direction. For example, the results returned from the above statement querying for restaurants serving pizza can be sorted first by the average rating (primary sort) and then by the title (secondary sort). In the modified version of the statement below, the results are first sorted by the average rating in descending order and then those restaurants with the same average rating are sorted by the title in ascending order.

select Title, Rating.AverageRating from local.search(20) where query="pizza" and city="New York" and state="NY" | sort(field="Rating.AverageRating", descending="true",field="Title", descending="false")

The following table lists the YQL functions that can be appended to a SELECT statement. Function arguments are specified as name-value pairs.

Function Argument Example Description
sort field [descending] [hidden] sort(field="nickname", descending="true", hidden="true" field="email") Sorts the result set according to one or more criteria that are specified by field keys. The default value of the optional descending argument is false. If hidden is set to "true", then the preceding field will be omitted from the items in the result set. Syntax: sort(field_1="value" [, descending_1="true"|"false"] [,hidden_1="true"|"false"], ... , field_n="value" [,descending_n="true"|"false"] [, hidden_n="true"|"false"])
tail count tail(count=4) Gets the last count items (rows).
truncate count truncate(count=4) Gets the first count items (rows).
reverse (none) reverse() Reverses the order of the items (rows).
unique field [hideRepeatCount] unique(field="Rating.AverageRating", hideRepeatCount="true") Removes items (rows) with duplicate values in the specified field (column). The first item with the value remains in the results. If hideRepeatCount is missing or set to "false", the item that remains in the results will be annotated with the element (XML) or field (JSON) yahoo:repeatcount specifying the number of items (including the returned one) with duplicate values in the specified field.
sanitize [field] sanitize(field='foo') Sanitizes the output for HTML-safe rendering. To sanitize all returned fields, omit the field parameter.

Table of Contents