SQL ORDER BY multiple fields with sub orders

Question

I'm trying to make a query to get the list of a students class ordered by the type defined, sex and name.

I have the next query:

SELECT * FROM STUDENTS
  ORDER BY FIELD(type, 'b', 'p', 'j', 'i', 'a', 'c', 'v'), FIELD(sex, 'M', 'F'), `name` ASC

I'm getting that order, first type, and then both sex from that type and name, but I need to define some types and sex together, something like this:

type = 'b', sex = 'M', name
type = 'b', sex = 'F', name
type = 'p', sex = 'M', name
type = 'p', sex = 'F', name
type = 'i', sex = 'M', name
type = 'a', sex = 'M', name
type = 'c', sex = 'M', name
type = 'v', sex = 'M', name
type = 'j', sex = 'M', name
type = 'i', sex = 'F', name
type = 'a', sex = 'F', name
type = 'c', sex = 'F', name
type = 'v', sex = 'F', name
type = 'j', sex = 'F', name

What is sex `H` ? – Juan Carlos Oropeza Nov 29 '17 at 13:07 — Juan Carlos Oropeza, Nov 29 '17 at 13:07
is there a column with type and sex ? – Damini Suthar Nov 29 '17 at 13:07 — Damini Suthar, Nov 29 '17 at 13:07
@JuanCarlosOropeza just a mistake, it's 'F' – Louis B Nov 29 '17 at 13:52 — Louis B, Nov 29 '17 at 13:52

Juan Carlos Oropeza · Accepted Answer · 2017-11-29T14:02:02.430

1

You can always use a CASE statement to create the expression you want

SQL DEMO

SELECT *
FROM Table1
ORDER BY CASE WHEN type = 'b' and sex = 'M' THEN 1
              WHEN type = 'b' and sex = 'F' THEN 2
              WHEN type = 'p' and sex = 'M' THEN 3
              WHEN type = 'p' and sex = 'F' THEN 4
              ....
              ELSE 9999
          END,
          name

edited Nov 29 '17 at 14:02

answered Nov 29 '17 at 13:09

Juan Carlos Oropeza

47,252
12
78
118

This case is ordering by name, doesn't care about type or sex. – Louis B Nov 29 '17 at 13:52
Nope, this case order first for `{type, sex}` combinatation and then for `name`, did you try it? – Juan Carlos Oropeza Nov 29 '17 at 13:55
Yep, and didn't work. It orders just by name and doesn't care about type neither sex. – Louis B Nov 29 '17 at 13:57
Because you didnt fill all the cases. That was the intent for `...` and if didnt match any `CASE` by default return `NULL` and nulls go first. But you can solve it by adding the `ELSE` condition. – Juan Carlos Oropeza Nov 29 '17 at 14:03

SQL ORDER BY multiple fields with sub orders

1 Answers1