SQL to show lower limit provided the upper limit-CodePudding

I have sample dataset like this:

rank	Upper_limit
1	2.11
2	3
3	4.5
4	8.1
5	9.7

My desired output is this

rank	Lower_limit	Upper_limit
1	0	2.11
2	2.11	3
3	3	4.5
4	4.5	8.1
5	8.1	9.7

How can I achieve it using T-SQL?

CodePudding user response：

Using the LAG() analytic function we can try:

SELECT [rank],
       LAG(Upper_limit, 1, 0) OVER (ORDER BY [rank]) AS Lower_limit,
       Upper_limit
FROM yourTable
ORDER BY [rank];

CodePudding user response：

You can use LAG to obtain the previous value and ISNULL to return 0 on the first row:

select [rank]
    , ISNULL(LAG(Upper_limit, 1) over (order by [rank]), 0) Lower_limit
    , Upper_limit
from tbl

CodePudding user response：

USING LAG() function, this can be possible:

CREATE TABLE RankTable ([Rank] INT, Upper_Limit DECIMAL (8,2));

INSERT INTO RankTable ([Rank], Upper_Limit)
SELECT 1, 2.11 UNION ALL
SELECT 2, 3    UNION ALL
SELECT 3, 4.5  UNION ALL
SELECT 4, 8.1  UNION ALL
SELECT 5, 9.7 ;

SELECT [Rank], 
       ISNULL(LAG(Upper_Limit, 1) OVER (ORDER BY [Rank]), 0) AS Lower_Limit, 
       Upper_Limit
FROM RankTable

Working Fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=395b137d2301538946e5dbb397caea90