Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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