How to use GROUP BY in a query (Beginner question)?

[WeberAlex]

The file name of my note is a UID (YYYYMMDDHHmm)
The full title of a note is the Heading 1
The YAML contains:
Category: (it varies for each note, e.g. Obsidian, Q&A, etc.)

I would like to create a table with the file name & the full title, for all my notes, grouped by category, as in the table below. It's okay if the output of query renders differently.

Obsidian
202208061729	Obsidian Dataview plugin
Q & A
202208061830	How to ask a question on GitHub about Dataview

Can I have some help with my query please. My query below does not work.

1. I don't know how how to use the GROUP BY query (and FLATTEN). I read the manual but I have no clue.
2. At the moment I use an inline field in front my my heading 1 Title:: This is a note full title and the Title:: part in front of the heading does not look good. Is there a more elegant way to achieve the same result, without duplicating the title into the YAML?

TABLE
	file.name AS "File",
	Title
WHERE
	Category
GROUP BY
	Category
SORT
	file.name ASC

Can you explain me in plain English if you do, so that I can follow? Many thanks in advance.

[WeberAlex]

After many trial and error. But I still have no clue why I need to use rows, FLATTEN and why file.name did not return a link (I had to use file.link instead).

TABLE
   rows.file.link AS "Name",
   rows.Title AS "Title"
FLATTEN
   Category
WHERE
   Category
GROUP BY
   Category
SORT
   file.name ASC

[mnvwvnm]

• file.name is a string (the title);
• file.link is a link (the "path" to the file)

They're two different things.
If you want to use a specific string in a field as a link to the note, you need to use something like link(file.link, field).

About your query:

• we don't know if you need FLATTEN command or not (to separate multiple values in Category?)
• after any GROUP BY ... command you need to use rows to see the array of values per field;
• in your query above, I think the SORT file.name ASC don't has any effect

[WeberAlex]

@mnvwvnm

They're two different things. If you want to use a specific string in a field as a link to the note, you need to use something like link(file.link, field).

Thanks for that.

• we don't know if you need FLATTEN command or not (to separate multiple values in Category?)

Correct. It makes not difference when I remove it because my file names are unique. I read the Doc again and your reply. I guess understand better. e.g. if I was sorting several books by the same author name, I would need to use FLATTEEN (this is the example used in the doc.)

• after any GROUP BY ... command you need to use rows to see the array of values per field;

I am clear on that now. Do I need to Prefix each column field. Eg.

TABLE
    rows.Title
    rows.Author
    rows.Date

Or only the first one (i.e. rows.Title)

• in your query above, I think the SORT file.name ASC don't has any effect

Correct again, but why? Can't I decide of a sort order?

[mnvwvnm]

Correct again, but why? Can't I decide of a sort order?

Because this is related with the previous thing: the group by.
In DQL queries order matters:

• the first lines (the column expressions after TABLE) are, in fact, the last things to be considered (what you define bellow - as the group command - affects the way you write these expressions;
• FROM defines the source;
• after FROM, any command as WHERE, FLATTEN, GROUP BY, SORT, etc. works in sequential order.
  Because that, you need to apply the prefix rows to your sort command: SORT rows.file.name ASC. But it can not work... depends on what you group...