Sorting Issues - single vs multiple use of same key

[filmgal]

The issue: sorting is creating groups:

1. 1st group=pages with single instance of keyA;
2. 2nd group=pages with multiple instances of keyA.

Result: sorting is alphabetical within the groups rather than within the entire list.

Example:
I use "watched:: 2021-07-29 with Jonnie" in my film pages.
When I've seen a movie twice, there will be two separate instances of the "watched::" key

• watched:: 2021-07-29 with Terry
• watched:: 2018-09-15 with Jonnie at TIFF.

The Problem
When I want to sort by "watched" desc to see which films I've seen most recently, I can't get the proper results because of the groupings.
table title, watched where watched!=null and contains(watched, "2021") sort watched desc

Expected Result:
The films I watched in July should come first. But because of these groupings, I get all the single-instances of "watched" first and then I get the multiple instances of the watched key, which means the films I saw in May are displayed before the other films I watched in July that have multiple "watched" keys.

[M-bot]

Within the current constraints of the query language you can't sort the data the way you described. There's an update coming in the future which should let you do this but for now maybe the solution below could work.

If you don't need to display the other times, you can sort the table by watched key by first sorting the watched key

table title, reverse(sort(watched))[0] as watched
where watched != null and contains(watched, "2021")
sort watched desc

Alternatively you could also flatten the list. This will sort within the list but means you'll have multiple rows for each watched entry

table title, watched
where watched != null and contains(watched, "2021")
flatten watched
sort watched desc

Hopefully one of those helps while we wait for the query language update.

[filmgal]

Thank you for the alternates. Unfortunately neither does the trick. I'll just have to wait for the update.

FIY just in case these are unexpected results:

Alternative 1

`table reverse(sort(watched))[0] as watched
where contains(watched, "2021")
sort watched desc`

This still groups by singles at top, multiples at bottom, and it also doesn't display the dates where the key is used only once.

Alternative 2

table watched 
where contains(watched, "2021")
flatten watched
sort watched desc

The second option exclusively displays pages that use they key more than once, ignoring singles completely. Also, it throws an error if there are only singles of the key. Which means that we can't use flatten unless we know for a fact that there are multiples, and it will only display multiples ignoring singles.

[M-bot]

Ah yeah for alternative 1 I had assumed everything was a list by accident

Alternative 2 I think changing the order of where and flatten so that flatten is first might work

I'm still ramping up on this language myself. I took another stab at it and I'll post it in a sec as another answer

[filmgal]

@M-bot Oh goodness, I don't expect you to spend any time figuring out my issue. That is too much. I mean bug reports, yeah, but if I can't do a very specific thing after you told me it couldn't be done at least until an update, then I just expect to wait until the update. Please do not spend any additional time on this (unless it is something of interest to you).

[M-bot]

Ah no worries! This is great for understanding how to evolve the query language. Its always hard to know how users use something without testing out what they're doing.

[M-bot]

I believe after messing around for a bit more this should give what you want

```dataview
table rows.watched as watched
flatten watched
where contains(watched, "2021")
sort watched desc
group by title
sort rows.watched desc
```

[filmgal]

@M-bot You are too kind. I'll give this a whirl tomorrow (I've had one too many whiskies tonight!)