On Tue, 14 Mar 2023 at 21:01, Alban Hertroys <haramrae@gmail.com> wrote:
> > On 7 Mar 2023, at 4:11, David G. Johnston <david.g.johnston@gmail.com> wrote:
> > TOP(city, ROW(population, land_area)) ?
>
> What should be the expected behaviour on a tie though?
Undefined. Same as having an ORDER BY on a column that's not unique.
The sort implementation effectively defines the order. David did
specify the ROW() idea as a means to add additional columns so that
the tiebreak could be done with some other deciding factor.
> Should the result be just the first of the maximums (or minimums) through some selection criterium (such as their
alphabeticalorder), should that give each of the tied results, or should there be a means to define that behaviour?
It's an aggregate function. There's only 1 return value per group. If
you didn't want that you'd likely want to use a window function such
as rank() and add an outer query and filter out anything that's not
rank 1.
David
