ROW_NUMBER() OVER (PARTITION BY *** ORDER BY ***) excluding NULL values

• sarath.tata

  SSCommitted

  Points: 1640

  More actions

  February 5, 2015 at 11:55 am

  #1775839

  I guess you might need to do something like this:

  SELECT * ,

  ROW_NUMBER() OVER ( ORDER BY ID ) RowNum

  FROM #test WHERE colC IS NOT NULL

  UNION

  SELECT * ,

  0 RowNum

  FROM #test WHERE colC IS NULL

  ORDER BY ID

• quantik

  SSC Rookie

  Points: 25

  More actions

  March 30, 2016 at 11:23 pm

  #1868747

  Here's one way to do it :

  SELECT

  CASE WHEN ColC IS NULL THEN NULL

  ELSE

  ROW_NUMBER() OVER (PARTITION BY CASE WHEN ColC IS NULL THEN NULL ELSE ColC END ORDER BY ColC) END AS RowNum

  FROM #Test

• davidstorfer

  Grasshopper

  Points: 19

  More actions

  June 15, 2016 at 10:24 am

  #1885203

  Thanks quantik! This last example is the only CASE stmt-based solution that worked for my more complicated table (many more rows and joins than the OP). The union likely would have worked also, but this is more graceful. All the other CASE stmt solutions wound up with crazy huge numbers in the hundreds and thousands.

  quantik (3/30/2016)

  ---

  Here's one way to do it :

  SELECT

  CASE WHEN ColC IS NULL THEN NULL

  ELSE

  ROW_NUMBER() OVER (PARTITION BY CASE WHEN ColC IS NULL THEN NULL ELSE ColC END ORDER BY ColC) END AS RowNum

  FROM #Test

• sqldevmahesh

  Grasshopper

  Points: 17

  More actions

  June 21, 2018 at 9:16 am

  #1994842

  Kwex - Thursday, September 29, 2011 4:30 AM

  Thanks guys for taking time to respond.I'm not sure what level of DDL you expect, but I hope this can suffice.IF OBJECT_ID('tempdb..#test') IS NOT NULLDROP TABLE #testGOCREATE TABLE #test (ID INT IDENTITY, colC INT ) ;INSERT INTO #test VALUES ( 111 ) ;INSERT INTO #test VALUES ( 222 ) ;INSERT INTO #test VALUES ( NULL ) ;INSERT INTO #test VALUES ( NULL ) ;INSERT INTO #test VALUES ( 555 ) ;INSERT INTO #test VALUES ( 666 ) ;INSERT INTO #test VALUES ( NULL ) ;INSERT INTO #test VALUES ( '777' ) ;SELECT * , ROW_NUMBER() OVER ( ORDER BY ID ) RowNumFROM #testThis is what comes outID colC RowNum----- ----- -------------1 111 12 222 23 NULL 34 NULL 45 555 56 666 67 NULL 78 777 8What I want to achieve is thisID colC RowNum----- ----- -------------1 111 12 222 23 NULL NULL4 NULL NULL5 555 36 666 47 NULL NULL8 777 5Thanks a bunch.

  select id,colC, (case when colc is not null
     then row_number() over (partition by (case when colc is not null then 1 else 0 end)
             order by id
            )
   end) as rownum
        from #test
        order by id