Order table by the total count but do not lose the order by names-CodePudding

I have a table, consisting of 3 columns (Person, Year and Count), so for each person, there are several rows with different years and counts and the final row with total count. I want to keep the table ordered by Name, but also order it by the total count, like this:

Person	Year	Count
John	2018	3
John	2019	2
John	sum	5
Bob	2017	2
Bob	2019	4
Bob	sum	6
Max	2017	3
Max	2019	5
Max	sum	8

So here, the rows are ordered by sum, but also grouped by the Person and ordered by year. When I am trying to order by sum, of course, both person and years are messed up. Is there a way to sort like this?

CodePudding user response：

You've stored those "total" rows as well? Gosh! Why did you do that?

Anyway: if you

compute rank for rows whose year column is equal to 'total' and
add case expression into the order by clause,

you might get what you want:

SQL> with sorter as
  2    (select name, cnt,
  3       rank() over (order by cnt) rnk
  4     from test
  5     where year = 'total'
  6    )
  7  select t.*
  8  from test t join sorter s on s.name = t.name
  9  order by s.rnk, case when year = 'total' then '9'
 10                       else year
 11                  end;

NAME YEAR         CNT
---- ----- ----------
John 2018           3
John 2019           2
John total          5
Bob  2017           2
Bob  2019           4
Bob  total          6

6 rows selected.

SQL>