Tags

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


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”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

Advertisements