SQL ordering behaviour by multiple conditions

Question

Assume we have the following People table:

  Name  | Id  | Employee type
-------------------------------
  Max   | 2   | Engineer
  Alice | 3   | Scientist
  Bob   | 1   | Scientist
  John  | 4   | Engineer
  Jane  | 5   | Engineer

and now we want to order them as follows: First, we have the Scientists, then the Engineers. The scientists should be ordered by name in ascending order and the engineers by id in descending order.

Hence, the result should be

  Name  | Id  | Employee type
-------------------------------
  Alice | 3   | Scientist
  Bob   | 1   | Scientist
  Jane  | 5   | Engineer
  John  | 4   | Engineer
  Max   | 2   | Engineer

Of course, I could first run a query for the Engineers and then another one for the Scientists. However, I am wondering whether there is another more elegant solution.

My question is now, how can we achieve that we an elegant query? Ideally, you can formulate is as a sqlalchemy query, but a simple SQL statement would also help a lot.

Answer 1

Using two case-statements in the order by clause:

select p.* from people p order by p.employee_type desc, 
      case when p.employee_type = 'Scientist' then p.name else '' end asc,
      case when p.employee_type = 'Engineer' then p.id else '' end desc

See fiddle