Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
select a.vals[LowerRange],LEAD(a.vals,1) over(order by vals)[UpperRange] from
(select 0 [vals]
union all
select vals from RangeValues)a
LikeLike
;with cte
as
(
select 0 vals
union all
select vals from #RangeValues
)
, cte1
as
(
select *,ROW_NUMBER() over (order by vals) rwnum
from cte
)
select a.vals,b.vals from cte1 a
left join cte1 b
on a.rwnum+1=b.rwnum
LikeLiked by 1 person
select isnull(lag(vals) over (order by vals),0) as lowerrange, vals as upperrange from RangeValues
union all
select max(vals) as lowerrange, null as upperrange from RangeValues
LikeLike
WITH T1 AS (
SELECT VALS,RANK() OVER(ORDER BY VALS) AS RN FROM (SELECT 0 VALS FROM DUAL UNION
SELECT VALS FROM RangeValues) A),
T2 AS (SELECT VALS,RANK() OVER(ORDER BY VALS) AS RN FROM (SELECT VALS FROM RangeValues UNION SELECT NULL FROM DUAL) B)
SELECT T1.VALS,T2.VALS FROM T1,T2 WHERE T1.RN=T2.RN;
LikeLike