T-SQL Query | [ The Range Puzzle ] – In this puzzle we have to create range starting from 0 to the first number and then so on. Please check out the sample input and expected output for details.

Sample Input

 Vals 125 350 270 140

Expected Output

 LowerRange UpperRange 0 125 125 140 140 270 270 350 350 NULL

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ```CREATE TABLE RangeValues ( Vals INT ) GO INSERT INTO RangeValues(Vals) VALUES (125), (350), (270), (140) ```

UPDATE – 20-Apr-2015 – Solution 1

 ``` -- --Solution 1 ;WITH CTE AS ( SELECT Vals , ROW_NUMBER() OVER (ORDER BY Vals ASC) rnk FROM ( SELECT 0 Vals UNION ALL SELECT Vals from RangeValues )a ) SELECT c.Vals LowerRange , (SELECT TOP 1 Vals FROM CTE c1 WHERE c1.rnk > c.rnk ) UpperRange FROM CTE c -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com