CREATE TABLE #t1 ( ID int, Furnace_life INT);

INSERT INTO #t1(ID,Furnace_life) VALUES (1,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (2,2)
INSERT INTO #t1(ID,Furnace_life) VALUES (3,3)
INSERT INTO #t1(ID,Furnace_life) VALUES (4,4) ---

INSERT INTO #t1(ID,Furnace_life) VALUES (5,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (6,2)
INSERT INTO #t1(ID,Furnace_life) VALUES (7,3) ---

INSERT INTO #t1(ID,Furnace_life) VALUES (17,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (18,2)



SELECT * FROM #t1 AS t ORDER BY t.ID ASC;


DROP TABLE #t1

i want to get max value from 1 to Next 1 / start From 1 to Next 1 /

CodePudding user response：

This is a classic gaps-and-islands question. There are a number of techniques.

Here is a fairly simple one.

• Use LAG to get the previous row's value.
• Create groups (islands) of values using a windowed conditional COUNT.
• Group up by that ID, take the MAX value and sort by it, taking only the highest value.

WITH PrevValues AS (
    SELECT *,
      Prev = LAG(t.Furnace_life) OVER (ORDER BY t.ID)
    FROM #t1 t
),
Groups AS (
    SELECT *,
      GroupId = COUNT(CASE WHEN t.Prev = t.Furnace_life THEN NULL ELSE 1 END) OVER (ORDER BY Furnace_life ROWS UNBOUNDED PRECEDING)
    FROM PrevValues t
)
SELECT TOP (1)
  Highest_Furnace_life = MAX(g.Furnace_life)
FROM Groups g
ORDER BY
  Highest_Furnace_life DESC;

db<>fiddle

CodePudding user response：

You can use this code if the data is in order

CREATE TABLE #t1 ( ID int, Furnace_life INT);

INSERT INTO #t1(ID,Furnace_life) VALUES (1,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (2,2)
INSERT INTO #t1(ID,Furnace_life) VALUES (3,3)
INSERT INTO #t1(ID,Furnace_life) VALUES (4,4) ---

INSERT INTO #t1(ID,Furnace_life) VALUES (5,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (6,2)
INSERT INTO #t1(ID,Furnace_life) VALUES (7,3) ---

INSERT INTO #t1(ID,Furnace_life) VALUES (17,1)
INSERT INTO #t1(ID,Furnace_life) VALUES (18,2)


CREATE TABLE #TMPTBL (ID INT ,MAX_Furnace_life INT)

SELECT * FROM #t1 AS t ORDER BY t.ID ASC;

DECLARE @Max_Furnace_life INT = 0
DECLARE @MAXID INT = 0
DECLARE @Furnace_life INT
DECLARE @ID INT

DECLARE db_cursor CURSOR FOR 

    SELECT ID , Furnace_life FROM #t1 AS t ORDER BY t.ID ASC

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @ID ,@Furnace_life

WHILE @@FETCH_STATUS = 0  
BEGIN  
      
    IF @Furnace_life < @Max_Furnace_life
    BEGIN
        INSERT INTO #TMPTBL (ID ,MAX_Furnace_life) VALUES (@MAXID ,@Max_Furnace_life)
        SET @Max_Furnace_life = 0
        SET @MAXID = 0
    END
    ELSE
    BEGIN
        SET @Max_Furnace_life = @Furnace_life
        SET @MAXID = @ID
    END

    FETCH NEXT FROM db_cursor INTO @ID ,@Furnace_life
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

-- For End Of Records
INSERT INTO #TMPTBL(ID ,MAX_Furnace_life) SELECT TOP 1 * FROM #t1 ORDER BY ID DESC
SELECT * FROM #TMPTBL

DROP TABLE #t1
DROP TABLE #TMPTBL