ORDER BY clause

You can  accomplish more complex sorting with CASE operator in the ORDER BY clause than that which is allowed by using the sorting on a set of columns. At sorting on ram column it is possible to do it in increasing or decreasing order. If you wish to deduce all over again medium-ram models, i.e. models which respond to W98, then these with maximum-ram (W2k), and then at last the lowest-ram models (W95), you could do it in the manner:

SELECT *
FROM PC
ORDER BY CASE
WHEN ram <= 32
THEN '3-W95'
WHEN ram <= 64
THEN '1-W98'
ELSE '2-W2k'
END

Digits ahead of OS name are placed with accordance to desirable order of sorting. Otherwise, ordering of text values in increase will be the following: W2k, W95, W98. Here is result of the above query (the column of sorting is capitalized):

There is even more interesting opportunity of sorting, namely, sorting on different columns depending on value in some column. For example, let us suppose that in W95 group we wish to execute sorting on speed column , in group W98 - on hd column , in group W2k - on price column. I.e. in each group characterized by OS in accordance with criteria described above, we need to do sorting on different columns. This problem, at first sight a noneasy one, is solved via the simple query with CASE operator in ORDER BY clause:

SELECT *
FROM PC
ORDER BY ram,
CASE
WHEN ram <= 32
THEN speed
WHEN ram <= 64
THEN hd
ELSE price
END

This is the result of above query:

Suggested exercises: 65