SQL PUZZLE | THE Order BY CASE Puzzle | SQL Interview Question

In this puzzle you have to sort data based on the Id but Id with 0 should always be the last row. Now the question is can you do that with a single order by column.

For more details please refer sample input and expected output.

Sample Input

Expected output

Script – DDL and INSERT sample data

--

CREATE TABLE SingleOrder
(
	 Id INT
	,Vals VARCHAR(10)
)
GO

INSERT INTO SingleOrder VALUES
(0,'All'),
(1,'Pawan'),
(2,'Avtaar'),
(3,'Kishan'),
(4,'Vaibhav'),
(5,'Ashutosh')
GO

SELECT * FROM SingleOrder
GO

--

SOLUTION 1 – SINGLE ORDER BY COLUMN

--

SELECT * FROM SingleOrder
ORDER BY CASE WHEN Id = 0 THEN MAX(ID) OVER (ORDER BY ID DESC)+1 ELSE ID END

--

OUTPUT 1

--

Id          Vals
----------- ----------
1           Pawan
2           Avtaar
3           Kishan
4           Vaibhav
5           Ashutosh
0           All

(6 rows affected)


--

SOLUTION 2 – ORDER BY two COLUMNS

--

SELECT * FROM SingleOrder
ORDER BY CASE WHEN Id = 0 THEN 2 ELSE 1 END,ID

--

OUTPUT 2

--

Id          Vals
----------- ----------
1           Pawan
2           Avtaar
3           Kishan
4           Vaibhav
5           Ashutosh
0           All

(6 rows affected)


--

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com