CODEWITHSUNDEEP
sqlsql-servergaps-and-islands
Yas Ka
Yas Ka

Reputation: 21

how to get max No between 1 to Next 1 in sql


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 /

enter image description here

Upvotes: 1

Views: 84

Answers (4)

JMabee
JMabee

Reputation: 2300

Although I see this question has an approved answer, I thought I would add this in.

You stated that you want the maximum value for each group that falls between 1 and the Next value of 1. So given that, I would assume using your test data you should get 3 rows back, as there are three rows with a value of 1.

I used the value of 1 to mark the start of each group and then used the ROW_NUMBER() function to find the last row of each group.

;WITH CTE AS
    (
    SELECT *, SUM(CASE WHEN Furnace_life = 1 THEN 1 ELSE 0 END) OVER(ORDER BY ID) GRP
    FROM #t1
    )
, GRPS as
    (SELECT * , ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY Furnace_life desc) RN
    FROM CTE
    )
SELECT ID, Furnace_Life
FROM GRPS
WHERE RN = 1

Upvotes: 0

N.Noori
N.Noori

Reputation: 46

You can use below script. Note than records must be sorted by ID!

CREATE TABLE #t2 ( ID int, Furnace_life INT, TopLife INT NULL);

INSERT INTO #t2
(
    ID,
    Furnace_life,
    TopLife
)
SELECT t.ID,t.Furnace_life,
      LAG(t.Furnace_life) OVER (ORDER BY t.ID) TopLife
    FROM #t1 t
ORDER BY ID


SELECT * FROM #t2 AS t WHERE t.Furnace_life = 1 AND t.TopLife IS NOT NULL

DROP TABLE #t1
DROP TABLE #t2

Upvotes: 3

Charlieface
Charlieface

Reputation: 71741

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

Upvotes: 0

Mostafa NZ
Mostafa NZ

Reputation: 382

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

Upvotes: 0

Related Questions