SQL Puzzle | The Order By Puzzle – 1

In this puzzle we need to present data in a particular Order. We need M at first Position, B at second position and then the other values based on their order. Please check the sample input and expected output.

Sample Input

Val
A
D
B
M
R

Expected Output

Val
M
B
A
D
R

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE ORDERByPuzzle ( Val VARCHAR(10) ) GO INSERT INTO ORDERByPuzzle VALUES ('A'), ('D'), ('B'), ('M'), ('R') GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1 | USING CROSS APPLY

 ```-- /* 1. | Solution 1 | Using Left JOIN | Create new dynamic structure at runtime using UNION ALL & SELECT */ SELECT O.* FROM ORDERByPuzzle O LEFT JOIN ( SELECT 'M' Val , 1 Ord UNION ALL SELECT 'B' Val, 2 Ord )x ON x.Val = O.Val ORDER BY ISNULL(Ord,100), O.Val -- ```

Output-1

 ```-- /*------------------------ /* 1. | Solution 1 | Using Left JOIN | Create new dynamic structure at runtime using UNION ALL & SELECT */ SELECT O.* FROM ORDERByPuzzle O LEFT JOIN ( SELECT 'M' Val , 1 Ord UNION ALL SELECT 'B' Val, 2 Ord )x ON x.Val = O.Val ORDER BY ISNULL(Ord,100), O.Val ------------------------*/ Val ---------- M B A D R (5 row(s) affected) -- ```

Solution – 2 | Order By Using Case and ORDER BY

 ```-- /* 2. | Solution 2 | Order By Using Case and ORDER BY */ SELECT * FROM ORDERByPuzzle O ORDER BY CASE WHEN Val = 'M' THEN 1 WHEN Val = 'B' THEN 2 ELSE 10 END, Val -- ```

Output-2

 ```-- /*------------------------ /* 2. | Solution 2 | Order By Using Case and ORDER BY */ SELECT * FROM ORDERByPuzzle O ORDER BY CASE WHEN Val = 'M' THEN 1 WHEN Val = 'B' THEN 2 ELSE 10 END, Val ------------------------*/ Val ---------- M B A D R (5 row(s) affected) -- ```

Solution – 3 | USING Order By IIF and ORDER BY ( SQL 2012 + )

 ```-- /* 3. | Solution 3 | Order By IIF and ORDER BY */ SELECT * FROM ORDERByPuzzle O ORDER BY IIF(Val = 'M',1,IIF(Val = 'B',2,10)), Val -- ```

Output-3

 ```-- /*------------------------ SELECT * FROM ORDERByPuzzle O ORDER BY IIF(Val = 'M',1,IIF(Val = 'B',2,10)), Val ------------------------*/ Val ---------- M B A D R (5 row(s) affected) -- ```

Solution – 4 | Using Left JOIN | Create new dynamic structure at runtime using APPLY Operator

 ```-- /* 4. | Solution 4 | Using Left JOIN | Create new dynamic structure at runtime using APPLY Operator */ SELECT o.* FROM ORDERByPuzzle o LEFT JOIN (VALUES ('M',1),('B',2) ) p(Val,Ord) ON o.Val = p.Val ORDER BY ISNULL(Ord,100), O.Val -- ```

Output-4

 ```-- /*------------------------ /* 4. | Solution 4 | Using Left JOIN | Create new dynamic structure at runtime using APPLY Operator */ SELECT o.* FROM ORDERByPuzzle o LEFT JOIN (VALUES ('M',1),('B',2) ) p(Val,Ord) ON o.Val = p.Val ORDER BY ISNULL(Ord,100), O.Val ------------------------*/ Val ---------- M B A D R (5 row(s) affected) -- ```

Other Reference Puzzles

 Another order by Puzzle – https://msbiskills.com/2016/08/19/sql-puzzle-the-order-by-puzzle/

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com