loading..
Русский    English
20:19
go

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:

Console
Execute
  1. SELECT * 
  2. FROM PC
  3. ORDER BY CASE 
  4.  WHEN ram <= 32 
  5.  THEN '3-W95'
  6.  WHEN ram <= 64 
  7.  THEN '1-W98'
  8.  ELSE '2-W2k' 
  9.  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):

code model speed RAM hd cd price
3 1233 500 64 5 12x 600
1 1232 500 64 5 12x 600
8 1232 450 64 8 24x 350
2 1121 750 128 14 40x 850
4 1121 600 128 14 40x 850
5 1121 600 128 8 40x 850
6 1233 750 128 20 50x 950
11 1233 900 128 40 40x 980
12 1233 800 128 20 50x 970
7 1232 500 32 10 12x 400
9 1232 450 32 10 24x 350
10 1260 500 32 10 12x 350

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:

Console
Execute
  1. SELECT * 
  2. FROM PC
  3. ORDER BY ram, 
  4.  CASE 
  5.  WHEN ram <= 32 
  6.  THEN speed
  7.  WHEN ram <= 64
  8.  THEN hd
  9.  ELSE price 
  10.  END

This is the result of above query:

code model speed ram hd cd price
9 1232 450 32 10 24x 350
10 1260 500 32 10 12x 350
7 1232 500 32 10 12x 400
1 1232 500 64 5 12x 600
3 1233 500 64 5 12x 600
8 1232 450 64 8 24x 350
2 1121 750 128 14 40x 850
4 1121 600 128 14 40x 850
5 1121 600 128 8 40x 850
6 1233 750 128 20 50x 950
12 1233 800 128 20 50x 970
11 1233 900 128 40 40x 980

Suggested exercises: 65
Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.