I would like to have the highest quantity and the corresponding fruit output for each basket.
Like this:
bask… fruit … max-count
####################
basket_1 … oranges … 5
basket_2 … apples … 6
Things I have tried
my dataview query looks like this:
```dataview
table
max(rows.count) AS C
group by bask
```
leads to this:
but I only get the number displayed.
Simply writing file.name behind it, would certainly be too easy.
is this even possible with “simple” dataview or does dataviewjs have to do it?
Try the following query, and see if that gives you the wanted result:
## List all candidates, sorted according to basket and count
```dataview
TABLE bask, fruit, count
WHERE bask AND count
SORT bask ASC, count DESC
```
## GROUP them, and pick the first in each set
```dataview
TABLE rows[0].file.link, rows[0].fruit, rows[0].count
WHERE bask AND count
SORT bask ASC, count DESC
GROUP BY bask
```
The trick to doing this, is that we need to make sure that the max value is at an index in the grouped set that we now. Therefore the first step is to produce an intermediate table to get the sorting correct, where the first line in each set is the one value we’re really wanting to get.
Then after finding that query, we can change it into the second variant, we’re we group on the bask, and pick out the first, aka rows[0], member of each the grouped entries. Now you can delete the first query, since it’s no longer needed, but I left it in here to show the process of first sorting it “correctly”, and then grouping and picking out the wanted value.
However, that would require a link between the fruits and the baskets. This normally means you’d need to actually link them together, but I had an idea and tried it out, and it actually worked.
```dataview
TABLE rows[0].file.link, rows[0].fruit, rows[0].count
FLATTEN link(bask) as basketLink
WHERE bask AND basketLink.type = "round" AND count
SORT bask ASC, count DESC
GROUP BY bask
```
This assumes that the value of the bask field is the exact name of the basket note, so that we can use that to create a link back to the basket, and then check it’s type.