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

Expected Output

Rules/Restrictions

• The challenge is to do it in a single SQL statement.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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