SQL to show lower limit provided the upper limit

Question

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?

It's helpful if you can explain your desire, i.e. how you want the `Lower_limit` and `Upper_limit` divined from the sample dataset. We shouldn't have to guess. — HABO, Jul 14 '22 at 16:56

Tim Biegeleisen · Accepted Answer · 2022-07-14T16:17:20.610

1

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];

edited Jul 14 '22 at 16:17

answered Jul 14 '22 at 15:34

Tim Biegeleisen

502,043
27
286
360

1

@ARC You were correct, I have updated my answer. – Tim Biegeleisen Jul 14 '22 at 16:18

score 0 · Answer 2 · answered Jul 14 '22 at 15:37

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

SQL to show lower limit provided the upper limit

2 Answers2