# SQL Find Minimum Value Greater than Previous Row

Posted on

### Question :

How do I find the minimum value greater than the previous row?
This code needs to be optimized to run over million rows and business keys, we may have 3-10 timevalue columns at most. I am thinking its some type of recursive CTE.

background: conducting slowly changing dimensions for many tables which are being denormalized. And trying to find proper begin dates.

Data Sample:

``````create table dbo.timetest
(
TimeTestIdentityId int primary key identity(1,1),
timevalue1 int,
timevalue2 int,
timevalue3 int
)

insert into timetest
values
(5,131,134,137),
(5,131,138,135),
(5,131,140,135),
(5,143,141,145),
(5,149,141,148),
(5,150,141,148),

(6,134,137,140),
(6,134,141,138),
(6,134,143,138),
(6,146,144,148),
(6,152,144,151),
(6,153,144,151)
``````

Final Values:

,etc

Row are ordered by TimeValue1.

Select MIN value from multiple columns

My solution using a cursor and a temporary table, I can imagine another solution, but it involves an expensive recursive query.

```CREATE TABLE #tv
(
id int NOT NULL,
timevalue int,
);
GO
```
```DECLARE @bk int, @tv1 int, @tv2 int, @tv3 int;
DECLARE @last_min int = 0;
DECLARE @last_bk int = 0;
DECLARE @id int;

DECLARE curtv CURSOR FORWARD_ONLY READ_ONLY FOR
FROM     timetest

OPEN curtv;
FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;

WHILE @@fetch_status = 0
BEGIN

-- First row of a businesskey, basically set counters to 0
IF @last_bk  @bk
BEGIN
SET @last_bk = @bk;
SET @last_min = 0;
SET @id = 0;
end

SET @id = @id + 1;

-- select minimum positive value
-- it could be replaced by the below inline UDF but I think it doesn't worth.
SELECT @last_min = min(v) + @last_min
FROM   (VALUES (@tv1-@last_min),(@tv2-@last_min),(@tv3-@last_min)) as t(v)
WHERE  v > 0;

INSERT INTO #tv VALUES (@bk, @id, @last_min);

FETCH NEXT FROM curtv INTO @bk, @tv1, @tv2, @tv3;
END

CLOSE curtv;
DEALLOCATE curtv;
GO
```
```-- one temp table has been generated you could add an index.
GO

SELECT * FROM #tv ORDER BY businesskey, id;
GO

IF OBJECT_ID('tempdb..#tv') IS NOT NULL DROP TABLE #tv;
GO```
```businesskey | timevalue
----------: | --------:
5 |       131
5 |       135
5 |       140
5 |       141
5 |       148
5 |       150
6 |       134
6 |       138
6 |       143
6 |       144
6 |       151
6 |       153
```

db<>fiddle here

Using a recursive CTE:

```IF OBJECT_ID('min_positive') IS NOT NULL
DROP FUNCTION min_positive;
GO

-- inline UDF you should add as many parameters as you need.
CREATE FUNCTION min_positive(@v1 int, @v2 int, @v3 int)
RETURNS TABLE AS
RETURN
SELECT MIN(v) AS minpos
FROM (VALUES (@v1),(@v2),(@v3)) AS t(v)
WHERE v > 0;
GO
```
```;WITH ct1 AS
(
-- set a rank for each businesskey
-- I need it for the anchor in the recursive part
RANK() OVER (PARTITION BY businesskey ORDER BY TimeTestIdentityId) rk
FROM   timetest
)
, ct2 AS
(
-- first row of each businesskey
SELECT businesskey, timevalue1, timevalue2, timevalue3, rk,
(SELECT minpos
FROM   min_positive(timevalue1, timevalue2, timevalue3)) AS lm
FROM ct1
WHERE rk = 1

UNION ALL

-- next row of the businesskey untill the end of the rank
SELECT ct1.businesskey, ct1.timevalue1, ct1.timevalue2, ct1.timevalue3, ct1.rk,
(SELECT minpos + ct2.lm
FROM min_positive(ct1.timevalue1-ct2.lm, ct1.timevalue2-ct2.lm, ct1.timevalue3-ct2.lm)) lm
FROM ct1
JOIN ct2
AND ct1.rk = ct2.rk + 1
)
FROM     ct2
GO
```

db<>fiddle here

``````with
row_number() over (partition by businesskey order by timevalue1,timevalue2,timevalue3) rn
from timetest
),
from cte1
where rn=1
union all
case when t2.timevalue1>t1.timevalue0 then t2.timevalue1 else 2147483647 end
from cte2 t1
and t1.rn+1=t2.rn
union all
case when t2.timevalue2>t1.timevalue0 then t2.timevalue2 else 2147483647 end
from cte2 t1
and t1.rn+1=t2.rn
union all
case when t2.timevalue3>t1.timevalue0 then t2.timevalue3 else 2147483647 end
from cte2 t1
and t1.rn+1=t2.rn
)
from cte2
order by 1,5;
``````

fiddle

Output:

``````businesskey timevalue1  timevalue2  timevalue3  rn  timevalue0
5           131         134         137         1   131
5           131         138         135         2   135
5           131         140         135         3   140
5           143         141         145         4   141
5           149         141         148         5   148
5           150         141         148         6   150
6           134         137         140         1   134
6           134         141         138         2   138
6           134         143         138         3   143
6           146         144         148         4   144
6           152         144         151         5   151
6           153         144         151         6   153
``````