order by importance then group by section

Question

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

I just don't get it. You have five documents, but your returned data has only three. Do you only want one row per section? If so, which one? Or is revision important? — Gordon Linoff, Mar 24 '19 at 16:19
@GordonLinoff @GordonLinoff added more explaination at the end of question, in crux i want to `ORDER BY importance DESC` **then** `GROUP BY section` so i can get the highest importance without duplicates of lower rows with importance and same section i guess same as this question https://stackoverflow.com/q/50114799/11142047 but i do not undertand how do i implement the `sum() of importance` to it — Joe Doe, Mar 24 '19 at 16:40
@forpas i use latest version of mariadb so i guess mysql 5.7 — Joe Doe, Mar 24 '19 at 17:01

forpas · Accepted Answer · 2019-03-24T17:22:19.730

This works on the later version of MySql:

with 
  grp as (
    select d.section, d.id, sum(i.importance) imp
    from docs d inner join docs_importance i
    on i.doc_id = d.id  
    group by d.section, d.id  
  ),
  cte as (
    select grp.id, grp.section, grp.imp
    from grp inner join (
      select section, max(imp) maximp
      from grp
      group by section
    ) t on t.section = grp.section and t.maximp = grp.imp 
  )
select d.id, d.section, d.rev, cte.imp importance 
from docs d inner join cte
on cte.id = d.id and cte.section = d.section

See the demo
Results:

| id  | section | rev | importance |
| --- | ------- | --- | ---------- |
| 2   | 1       | b   | 6          |
| 3   | 2       | c   | 9          |
| 5   | 3       | e   | 6          |

For older versions:

select d.id, d.section, d.rev, cte.imp importance 
from docs d inner join (
    select grp.id, grp.section, grp.imp
    from (
    select d.section, d.id, sum(i.importance) imp
    from docs d inner join docs_importance i
    on i.doc_id = d.id  
    group by d.section, d.id  
  ) grp inner join (
      select section, max(imp) maximp
      from (
    select d.section, d.id, sum(i.importance) imp
    from docs d inner join docs_importance i
    on i.doc_id = d.id  
    group by d.section, d.id  
  )grp 
  group by section
    ) t on t.section = grp.section and t.maximp = grp.imp 
  )cte
on cte.id = d.id and cte.section = d.section

See the demo

can it be done in normal sql for older versions too? i do not quiet understand the `with as` statements yet so i may make some mistakes in future edits on it — Joe Doe, Mar 24 '19 at 17:19

order by importance then group by section

1 Answers1