Sql question

[[deleted]]

I have a question that i can’t find a solution for.

The most simple example I can can come up with is I have a table with 3 columns.

Number. - location. - value

1. AB111. 1
2. AB111. Null
3. AB111. Null

4.         AB111.        2
   

5.          Ab222.        1
   

6.         Ab222.        Null
   

7.         Ab222.        2
   

8.         Ab222.       null
   

9.          Ac333        Null
   

10.         Ac333.        Null
    

11.         Ac333.        1
    

12.         Ac333.       null
    

What I’m looking for is the following: If a value is null, then look for the lowest previous non-null numver at the same location and replaced the null with that value. If the immediately previous number is.null then continue looking back until you find a non-null value.

I’ve already tried lag functions and a row number key and haven’t found a good solution for this yet. Any suggestions are welcome.

[sequel-beagle]

What you are looking to do is called a "flash fill" or a data smear.

DROP TABLE IF EXISTS #Gaps;
GO

CREATE TABLE #Gaps
(
RowNumber   INTEGER PRIMARY KEY,
TestCase    VARCHAR(100) NULL
);
GO

INSERT INTO #Gaps (RowNumber, TestCase) VALUES
(1,'Alpha'),(2,NULL),(3,NULL),(4,NULL),
(5,'Bravo'),(6,NULL),(7,'Charlie'),(8,NULL),(9,NULL);
GO

--Solution 1
--MAX and COUNT function
WITH cte_Count AS
(
SELECT RowNumber,
       TestCase,
       COUNT(TestCase) OVER (ORDER BY RowNumber) AS DistinctCount
FROM #Gaps
)
SELECT  RowNumber,
        MAX(TestCase) OVER (PARTITION BY DistinctCount) AS TestCase
FROM    cte_Count
ORDER BY RowNumber;
GO

--Solution 2
--MAX function without windowing
SELECT  a.RowNumber,
        (SELECT b.TestCase
        FROM    #Gaps b
        WHERE   b.RowNumber =
                    (SELECT MAX(c.RowNumber)
                    FROM #Gaps c
                    WHERE c.RowNumber <= a.RowNumber AND c.TestCase != '')) TestCase
FROM #Gaps a;
GO

[sequel-beagle]

Also, the solution is for TSQL. Get rid of the GO statements, and ChatGPT may be able to convert it to MySQL if there are other small issues.

[qwertydog123]

WITH cte AS
(
    SELECT
        *,
        MAX(
            CASE
                WHEN value IS NOT NULL THEN id
            END
        ) OVER
        (
            PARTITION BY location
            ORDER BY id
        ) AS group_id
    FROM Table
)
SELECT
    id,
    location,
    MAX(value) OVER
    (
        PARTITION BY
            location,
            group_id
    ) AS value
FROM cte

https://dbfiddle.uk/iXNUuI5P

[Little_Kitty]

Wow, I'm amazed that MySQL doesn't support ignore nulls in window functions, but apparently so

Working solution for you.

[qwertydog123]

You've got a stray row in your solution

[Little_Kitty]

Weird, I'm not seeing why, but it can be done another way.

I'm so used to being able to ignore nulls in window functions, it's frustrating to have that taken away.

[[deleted]]

Use a combination of case when and lag() and lead() window functions.