ESQL: make order in TOP optional

[alex-spies]

The third argument to TOP has to be either "asc" or "desc". This is cumbersome to write for the default case.

Let's make it optional and have it default to "desc", so that e.g. TOP(salary, 5) returns the highest 5 values for salary. Edit: see below for a discussion of the best default.

Relates #112386.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[jan-elastic]

Not sure whether asc or desc should be the default.

TOP(salary, 5) returning the top salaries sounds appealing, but then TOP(rank, 5) returns the lowest ranks, and TOP(name, 5) the lexicographically last names

[quackaplop]

I vote for ASC, as it is compatible with the default SORT order and is also pretty common in most RDBMS

[wchaparro]

@tylerperk ?

[alex-spies]

@tylerperk , do you know of a use case that would suggest that this should default to "desc"? Otherwise, it looks like we want to go with TOP(field, 10) being syntactic sugar for TOP(field, 10, "asc").

[tylerperk]

There are certainly use cases where DESC would be the preferred default (usually when the field is a number like the salary example above) but others where ASC would be preferred - so it seems like a situation where we can't please everyone with one default - hence it makes sense to go for consistency (and chose ASC). Unless we think it makes sense to default to DESC for numbers and dates, and ASC for keyword, text, IP...