Using SQL ORDER BY for complex criteria

Question

Let's say my relation is like so

and I wanted to sort by rank, but A's and B's first. So my relation will look like

At first I tried to just have two relations, where I order both of them by rank, then union the two relations, but they just end up getting jumbled together. I've also tried using cases, but I don't understand how it works. I kept getting errors about mixing characters with integers.

Edit: Forgot to mention that the first relation above is what I end up with after a somewhat complex number of views. For example, I created two views, one which only has A and B classes, then I ordered that by rank. Second view just C classes, ordered that by rank. Then I unioned them, but they just get mixed.

Answer 1

You only need a CASE expression in the ORDER BY, to divide the rows into two groups, so the ones with class 'A' or 'B' are put in first group (1) and the all the others in the second (2):

ORDER BY 
    CASE WHEN class IN ('A', 'B') THEN 1 ELSE 2 END,
    rank ;

Answer 2

I'm going to give you one example of a way to do it, where I use CTEs, which are available in many databases, particularly PostgreSQL which I am most familiar with.

CTEs and the WITH clause

CTEs allow you to form temporary, single-query table like structures, but since you can make CTEs rely on one-another in a single query, it provides a convenient and easy-to-understand way to make your query semi-procedural.

In your case, I would try

WITH AB_results AS(
  SELECT class, rank, 1 AS hidden_rank
  FROM my_relation
  WHERE class IN ('A','B')
),
Non_AB_results AS(
  SELECT class, rank, 2 AS hidden_rank
  FROM my_relation
  WHERE class NOT IN ('A','B')
)
SELECT COALESCE(AB_results.class,Non_AB_results.class), rank
FROM AB_results
FULL OUTER JOIN Non_AB_results
USING(hidden_rank,rank)
ORDER BY hidden_rank, rank;

I'll try to put together a SQLFiddle in a couple minutes here, just to make sure I didn't get some of my query wrong. :P

EDIT: I did make a small error. Trying to clean it up in an example as we speak.

EDIT 2: The error is now corrected! Unfortunately, SQLFiddle is having a little trouble right now, but HERE is the link to the SQLFiddle for some testing.

Please note that I may not have solved this in the most elegant possible way, but it should solve your issues.