1

Sample

I want to get the maximum consecutive occurrence of null in a column as shown in the screenshot. As there is ID column also there in this table, ordering could be done based on it. Like in this case, the output would be 4 as maximum 4 nulls are together.

SELECT * INTO #tmp FROM (Select 1 AS ID, 1 as lvl union all
Select 2, 1 union all
Select 3, Null union all
Select 4, Null union all
Select 5, 1 union all
Select 6, 1 union all
Select 7, Null union all
Select 8, Null union all
Select 9, Null union all
Select 10, Null union all
Select 11, 1 union all
Select 12, 1 union all
Select 13, 1 union all
Select 14, 1)x
SELECT * FROM #tmp
DROP TABLE #tmp

I just dont know from where to start, so any help would be appreciable.

Hemant Sisodia
  • 488
  • 6
  • 23
  • I just realized that there is a flaw in your question. There is no internal order in the `Lvl` column. Contiguous groups of `NULL` values only exist with regard to some other column. That being said, can you let us know how the `NULL` values are being conceptually grouped together? – Tim Biegeleisen Dec 18 '15 at 08:12
  • @TimBiegeleisen, the question states that there is an ID column that can be used for ordering, and the example data provides the same. –  Dec 18 '15 at 08:14
  • @dan1111 Thanks for pointing this out, let me try again. – Tim Biegeleisen Dec 18 '15 at 08:14
  • 1
    Please have a look at [this SO post](http://stackoverflow.com/questions/27252235/count-consecutive-duplicate-values-in-sql). Gordon Linoff wrote it, so I'm sure it works. You will have to tweak it slightly for your query. – Tim Biegeleisen Dec 18 '15 at 09:35
  • @TimBiegeleisen Thanks, that post helped me in getting the desired result. – Hemant Sisodia Dec 18 '15 at 09:44
  • 1
    @TimBiegeleisen Yup, did it already, thanks for the link. Cheers! – Hemant Sisodia Dec 18 '15 at 09:46

1 Answers1

1

Following the post at this link, I found the solution of my problem. The query giving correct result as 4 is:

SELECT TOP 1 COUNT(*)AS MaxNull
FROM
   #tmp t
   JOIN #tmp t2 ON t2.id <= t.id
WHERE NOT EXISTS(
               SELECT 1
                 FROM #tmp t3
                 WHERE t3.id BETWEEN t2.id AND t.id
                   AND NOT t3.lvl IS NULL)
GROUP BY t.id
ORDER BY COUNT(*)DESC;
Hemant Sisodia
  • 488
  • 6
  • 23