SQL Puzzle | The Order By Puzzle

The requirement is you have to write a single SQL statement to achieve the desired result.

For details please check out the sample input and the expected output below-

Sample Inputs

val
0
-10
NULL
NULL
NULL
1
5
-7

Expected Output

val
-10
-7
0
1
5
NULL
NULL
NULL

Rules/Restrictions

• The challenge is to do it in a single SQL statement.

Script | use below script to create table & insert some sample data

```
--

CREATE TABLE Orders (ID INT)
GO

INSERT INTO Orders (ID) VALUES
(0),
(-10),
(NULL),
(NULL),
(NULL),
(1),
(5),
(-7)
GO

--

```

Solution 1

 ``` -- SELECT Id FROM Orders ORDER BY id * -1 DESC -- ```

Solution 2

 ``` -- Select * from Orders order by Case when ID is null then 1 else 0 end, ID -- ```

Solution 3

 ``` -- select * from orders order by isnull(ID,999999999) asc -- ```

Solution 4

 ``` --Not a single select but I like the logic, thats why I put it here.. SELECT A.ID FROM ORDERS A, (SELECT MAX(ID) AS ID FROM ORDERS) B ORDER BY ISNULL(A.ID,B.ID) -- ```

Solution 5

 ``` -- select * from Orders order by Case When ID is Null Then ASCII('Z') Else ID End -- ```

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com