2

In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0

id  section     rev     importance
2   1           b       6
1   1           a       5
3   2           c       9
5   3           e       6
4   3           d       5

i want to order by **importance** then group by **section**

so i tried to use GROUP BY docs.section

SELECT docs.id, docs.section, docs.rev, (
  SELECT SUM(docs_importance.importance) 
  FROM docs_importance
  WHERE docs_importance.doc_id = docs.id
) AS importance
FROM docs
GROUP BY docs.section    ---GROUPING
ORDER BY docs.section, importance DESC

but got this result:

id  section     rev     importance
1   1           a       5
3   2           c       9
4   3           d       5

but the result i want is supposed to be

id  section     rev     importance
2   1           b       6
3   2           c       9
5   3           e       6
1
  • 1
    Follow the greatest-n-per-group tag -- it leads to several solutions. You cannot do your task with a simple GROUP BY. (Meanwhile, the ORDER BY is a red herring and could be ignored.) – Rick James Apr 16 '19 at 18:04
1

i used this query

SELECT t.id, t.section, t.rev, t.importance FROM (
   SELECT docs.id, docs.section, docs.rev, (SELECT SUM(docs_importance.importance) FROM docs_importance WHERE docs_importance.doc_id = docs.id) AS importance
   FROM docs
   GROUP BY docs.id
   ORDER BY importance DESC
) AS t
GROUP BY t.section

where i first made a subquery as a temporary table then ordered it by importance then grouped the outer query by section to get the final result.

0

I believe you can't do what you're thinking you want to do.

Let me put it this way: if you want to "order by importance first, then group by section", tell me how you plan to group the rows for section = 1 when one row has importance = 5 and the other has importance = 6?

Do you see the problem? It makes no sense. How can I order all the importance = 6 first, and then somehow group by rows that you've split apart because of your ordering (ie. where section = 1)?

This is probably why the SQL syntax requires your group by clause first, and your order by clause second - because grouping happens first, and ordering happens second.

Interestingly, you noted that you tried this SQL:

SELECT docs.id, docs.section, docs.rev, (
  SELECT SUM(docs_importance.importance) 
  FROM docs_importance
  WHERE docs_importance.doc_id = docs.id
) AS importance
FROM docs
GROUP BY docs.section    ---GROUPING
ORDER BY docs.section, importance DESC

... but when I paste this into your Fiddle and run, I get an error:

Unknown column 'GROUPING' in 'group statement'

Perhaps check on exactly how you got the results you gave?

3
  • the part of ---GROUPING is supposedly a comment i used --- since it grayed the text. – Joe Doe Mar 25 '19 at 7:47
  • 1
    Sorry, yes, that error was trivial and I was out of time to look in detail. What I expected was an error because you are selecting columns (id, rev) that are not contained in your group by clause - how should the DMBS pick values? I was surprised to learn MySQL allows this. Other DMBSes don't. See this answer elsewhere for a discussion on this: stackoverflow.com/a/24257983/3714936 – youcantryreachingme Mar 26 '19 at 2:09
  • Need space after -- to avoid the error message. – Rick James Apr 16 '19 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.