I'm pulling Chapter Titles, Section Titles, and Questions from my tables to make a table of contents. I am using Caspio's low code platform, which runs on SQL-Server.
I've been able to GROUP BY or ORDER BY, but I can't seem to do both. For my purposes, ORDER BY is way more important. GROUP BY will just make it easier to handle. When I add a GROUP BY statement it shows nothing.
I want to be able to GROUP BY or use DISTINCT, do you see any reason why cannot?
Here is my query:
SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
FROM _v_Questions_View as QV
WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]'
AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID] '
ORDER BY QV.WF_tbl_Chapter_Position, QV.WF_tbl_Section_Position, QV.WF_tbl_Question_Position
for xml path(N'')
Here is an example of a query that fails to show anything:
SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
FROM _v_Questions_View as QV
WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]'
AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID]'
GROUP BY [@field:WF_tbl_Chapter_Title], [@field:WF_tbl_Section_Title], [@field:WF_tbl_Question_Description]
ORDER BY QV.WF_tbl_Chapter_Position, QV.WF_tbl_Section_Position, QV.WF_tbl_Question_Position
for xml path(N'')
Search Stack Overflow for questions about distinct with "for xml" in sql server. Caspio uses sql server.
Answers commonlly advise isolating the initial select in a subquery or CTE.
So a guess.... wrap your working query like one of these:
SELECT DISTINCT Chapter, Section, Question FROM
(SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
FROM _v_Questions_View as QV
WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]'
AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID] '
) AS SUBQ
ORDER BY Chapter, Section, Question
for xml path(N'')
or
WITH X AS
(SELECT [@field:WF_tbl_Chapter_Title] as Chapter, [@field:WF_tbl_Section_Title] as Section, [@field:WF_tbl_Question_Description] as Question
FROM _v_Questions_View as QV
WHERE [@field:WF_tbl_Workbook_Author_ID] = '[@authfield:WF_tbl_Customer_Customer_ID]'
AND [@field:WF_tbl_Workbook_Workbook_ID] = '[@WID] '
GROUP BY [@field:WF_tbl_Chapter_Title], [@field:WF_tbl_Section_Title], [@field:WF_tbl_Question_Description]
)
SELECT Chapter, Section, Question
FROM X
ORDER BY Chapter, Section, Question
for xml path(N'')