Quality of Life - Top Aggregate Function

[Jm-Fox]

The top aggregate function currently (version 2024.2) has two arguments.

I propose the top function to have three parameters:

1. The column to select (as it is today)
2. The N value count to select (as it is today)
3. New optional string: what to name a third column for N.

This change would be non-breaking, as it is just adding an overload. Providing null to the third parameter would yield the same behavior as two parameters (v 2024.2).

Purpose: It would be easier to review results in Excel when exporting a .csv. This is a pretty niche request; I only make it because it seems like it would be simple.

Example input data:

+-------+--------------------------------------+
| Name  | Id                                   |
+-------+--------------------------------------+
| Alice | 383980f4-6f48-4ae1-9aa9-3c0feaac7de2 |
+-------+--------------------------------------+
| Alice | 82dc6805-3c46-4063-8cae-44e6959800c7 |
+-------+--------------------------------------+
| Bob   | b77734b5-d0a0-4cd3-a674-08a818b99436 |
+-------+--------------------------------------+

Example proposed query: select top(Id, 2, 'Index') from stream group by Name Example proposed result:

+-------+--------------------------------------+-------+
| Name  | top(Id, 2, true)                     | Index |
+-------+--------------------------------------+-------+
| Alice | 383980f4-6f48-4ae1-9aa9-3c0feaac7de2 | 1     |
+-------+--------------------------------------+-------+
| Alice | 82dc6805-3c46-4063-8cae-44e6959800c7 | 2     |
+-------+--------------------------------------+-------+
| Bob   | b77734b5-d0a0-4cd3-a674-08a818b99436 | 1     |
+-------+--------------------------------------+-------+

Additionally, it would be nice if the documentation had some details on sorting. In particular, the documentation doesn't answer "the first N values" defined as most recent? Oldest? Can this order be altered?

[nblumhardt]

Thanks for the suggestion, we'll give this some thought. I'll loop back with more information on the sort behavior 👍

[nblumhardt]

Added a short note that the default ordering for select queries on stream is time-ascending. Thanks for the nudge! https://docs.datalust.co/docs/aggregate-operators#top

[nblumhardt]

Thanks for raising this one; while it's a useful addition, we'd rather avoid special-casing top and enable row numbering with a more general feature in the future, if/when there's an opportunity. Thanks again!