How to see matching rows for aggregates max/min?

[frosencrantz]

I really like how the frequency chat links back to the rows in the original sheet for selection or dive row takes you to a sheet with the matching rows.

When you are using an aggregates max/min on a column is there an easy way to either select only the rows that match the max/min or dive to a sheet with rows that match the max/min?

Like here I add a max to a column, but can Visidata select the rows that match the max in the original sheet:
https://asciinema.org/a/a4V1fXbZEaowP2Rn93M8ckfkX?t=10

[danadam]

Maybe there's a simpler way, but I've just started using the tool, so you know... :-)

Given:

Category	Value	Description
a	1	This is not max
a	3	This is max
a	2	This is not max
b	8	This is not max
b	9	This is max
b	7	This is not max

• add "max" aggregator to "Value" column (+)
• set "Category" as a key column (!), will be needed later for joining
• create Frequency sheet on "Category" column (Shift+f)
• on Frequency sheet, remove "count" column (-)
• on Frequency sheet, make sure "Category" column is set as a key column (it should be by default)
• open Sheets sheet (Shift+s)
• select our original sheet and the Frequency sheet (s)
• inner join the sheets (&)
• select rows matching expression Value == Value_max (z|)
• filter selected rows (")
• remove "Value_max" column (-)

The result should be now:

Category	Value	Description
a	3	This is max
b	9	This is max

[frosencrantz]

Hi @danadam! Happy to see your response. Thank you for taking the time to respond. I haven't had a chance to try this out, but it seems like it might work. Will it work if there is more than one max per category? Like if in your sample table the first row also said "This is max"

It does seem like a lot of steps, I did want something that was a little easier. The Frequency Sheet provides the ability to make changes directly on the source sheet. I like your solution since it uses joining back to the source sheet. Maybe if there was an easier way to do that join given VisiData already knows what the indexes you used to generate the Frequency Sheet.

Saul has recently added some code for providing a window of values, but I haven't spent the time to look at it either. I thought it would be useful if VisiData had something like the SQL window function.

[danadam]

Will it work if there is more than one max per category?

Yes.

Like if in your sample table the first row also said "This is max"

Well, not like that. We don't compare on "Description" column, we only take "Description" corresponding to max "Value". So, if we had another row with value 3, like:

Category	Value	Description
a	3	This is another max

then the result would be:

Category	Value	Description
a	3	This is max
a	3	This is another max
b	9	This is max

[frosencrantz]

Thanks @danadam I think that is a useful way to use the Frequency sheet to join back to the original sheet. I haven't done this query recently, but when I do I think this will be useful.

I am interested in having the rows in the original sheet selected, while still keeping the other non-max rows. I want to use selection expansion as discussed in #1327 I think an outer join can be used in this case, and select rows that have values in the max column.