SQL Puzzle: SQL Advance Query - Generate the data in the RANGE format

SQL Puzzle: SQL Advance Query – Generate the data in the RANGE format

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to start the range from 0 to first value and the first value to second value so on.

Input Data:

Val

-----------

108

116

226

308

416

426

Expected Data:

LowerRange UpperRange

----------- -----------

0 108

108 116

116 226

226 308

308 416

416 426

426 NULL

Create a table with sample data:

CREATE TABLE Ranges

(Val INT)

INSERT INTO Ranges(Val)

VALUES

(108),(116),

(226),(308),

(416),(426)

Solution 1:

;with CTE as

(

select *,row_number()over (order by Val) as rnk from (select 0 Val union select Val from Ranges) AS T

)

select a.Val as LowerRange,b.Val as UpperRange

from CTE a

left outer join CTE b on a.rnk = b.rnk-1

Solution 2:

;WITH CTE AS

(

SELECT Val , ROW_NUMBER() OVER (ORDER BY Val ASC) rnk FROM

(

SELECT 0 Val

UNION ALL

SELECT Val from Ranges

)

SELECT c.Val LowerRange , (SELECT TOP 1 Val FROM CTE c1 WHERE c1.rnk > c.rnk ) UpperRange

FROM CTE c

Please try the different solution for this puzzle and share it via comment...

Mar 11, 2019Anvesh Patel

Comments: 5

HIC

April 6, 2019 at 7:58 pm

with a as (
select rownum as id, val
from ranges
)
, b as (
select (rownum + 1) as id, val
from ranges
)
select nvl(b.val, 0) as LowerRange, a.val as UpperRange
from a
full join b on b.id = a.id
order by LowerRange;

Reply Cancel

Sai Nath

August 11, 2019 at 8:13 am

; WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER( ORDER BY (SELECT 1)) AS RW
FROM
Ranges
)

SELECT
A.VAL,
B.VAL
FROM
CTE AS A
LEFT JOIN
CTE AS B ON A.RW = B.RW-1

UNION

SELECT
B.VAL,
A.VAL
FROM
CTE AS A
LEFT JOIN
CTE AS B ON A.RW-1 = B.RW

Reply Cancel

koustav modak

October 30, 2019 at 12:46 pm

(select a1.val LowerRange ,a2.val UpperRange from
(select row_number() over() as rnk,a.* from
(select * from
(select 0 val from Ranges
union
select Val from Ranges)T
order by 1)a )a1,
(select row_number() over() as rnk,a.* from
(select * from
(select 0 val from Ranges
union
select Val from Ranges)T
order by 1)a )a2
where a1.rnk+1=a2.rnk)
union all
(select Val,null from Ranges order by 1 desc limit 1)