Tags

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


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

Advertisements