Dataview Table showing MOST RECENT Note

[Twita]

Things I have tried

What I’m trying to do

I’m trying to create a dataview table that shows only my MOST RECENT review of any given restaurant (along with the date, etc). Earlier reviews would be excluded.

Date	Restaurant	Review
1/5/22	ABC	Loved this place
1/8/22	DEF	Ugh - wouldn’t eat here again

I’ve combed through the Dataview literature thinking that the answer was likely in a WHERE or FLATTEN argument, but haven’t had luck. I’m guessing there’s a simple answer but not sure ! Thanks…

[Craig]

Hi @Twita, could you please give a couple examples of what your source Markdown files look like? Are they organized by day, by restaurant, by something else? What does your current query look like?

[Twita]

Hi Craig:
Thanks for responding !
I have numerous restaurant review notes that are all named and formatted in the same style.

1. The name is the date followed by the restaurant name.
2. YAML data defines the Restaurant and a Rating
3. A summary is included in each note using :: which is something I’d likely want to capture in a query.

As for the query, I don’t have any idea how to only get the most recent review. I can get all reviews fairly easily with

Table Restaurant, Rating, Summary
From ""
Where Restaurant != null

Example File: 22-01-05 ABC Kitchen

YAML Frontmatter:
Restaurant: ABC Kitchen
Rating: 3

Summary:: While the appetizers were huge…

General Note: Blah blah blah.

Thanks for the help !!!

[Shiinii]

I don’t get right what you want but this might help

In dataview documentation has things that might interest you:

1. file.ctime The date that the file was created (a date + time).
2. file.mtimeThe date that the file waa modified (a date + time).
3. SORT Works similary like “WHERE”, you select the argument and the orientation, and it will sort the files.
4. FROM Especify where it is gonna search the files, from “folder” or #tags.

Exemple:

Table Restaurant, Rating, Summary
From "Review"
Where Restaurant != null
SORT file.ctime DESC

This will make a table, from the folder “Review”, organized by decrement bases in its creation time.

• Full Documentation

[Twita]

Thanks for that. To be clear, my goal is to only see ONE note per restaurant…the most recent !

[scholarInTraining]

@Twita I could not figure out how to do this in DQL (“regular dataview”) but if you instead of a dataview codeblock you use a dataviewjs one, then something like the following should work:

let finalTbl = [];
dv.pages("").where(p => p.Restaurant).groupBy(p => p.Restaurant).forEach(
group => finalTbl.push(group.rows.sort(note => note.file.mtime, 'desc').first())
); /* end forEach */
dv.table(
["Restaurant", "Rating", "Summary"],
dv.array(finalTbl).map(row => [row.restaurant, row.rating, row.summary])
);

You’ll have to enable Javascript in your Dataview Settings page if you haven’t before.
One note: the /* end forEach */ is a comment because I was having issues lining up parentheses. You can remove it without any effect on the code (though before you do, might as well double-check my parenthesis counting from the first use of forEach to the comment!).
I’d be happy to go through and explain any or all bits of this code later today, when I have a few more minutes. In the meantime, would love to see if any experts have a way to simplify it or to do this in regular DQL!

[Shiinii]

Use LIMIT

Using limit 1 afther you are gonna have only one.

Table Restaurant, Rating, Summary
From "Review"
Where Restaurant != null
SORT file.ctime DESC
LIMIT 1

[Craig]

Thanks for posting your data. It’s a little hacky, but sorting by date, using GROUP BY, and then picking the first row might get you what you’re looking for. Here’s my query:

```dataview
TABLE 
	rows[0].Rating as Rating, 
	rows[0].Summary as Summary, 
	rows[0].file.day as Date
FROM "Scratch"
WHERE Restaurant
SORT file.day DESC
GROUP BY Restaurant
```

And here’s what it looks like on my machine:

[Twita]

Kudos to all of you for taking time to help educate those of us who are still learning ! Obsidian gets more useful / indispensable for me with every Help post I read…

[scholarInTraining]

Do you have a way to make LIMIT 1 work per-group? (Or in this case per-restaurant?) When I use it I only get one answer back at all, not one per-restaurant. Thanks!

[Shiinii]

I dont Know

The best answer for you is what Craig has done, it work for you question.

[Twita]

Works perfectly - thank you Craig !
The LIMIT 1 does not accomplish the goal…