CTE and Rank by NULL columns

1

Need to RANK data-set by each row in table, number of NULL columns in DESC order.

Been playing around with this syntax below, but still not giving me what I need. Any ideas how to achieve the objective?

WITH sampledata (Value, CanBeNull)
AS
(
SELECT 1, 1
UNION
SELECT 2, 2
UNION
SELECT 3, NULL
UNION
SELECT 4, NULL
UNION
SELECT 5, NULL
UNION
SELECT 6, NULL
)
SELECT
DENSE_RANK() OVER (PARTITION BY CanBeNull ORDER BY CASE WHEN CanBeNull IS NOT NULL THEN value END ASC) as RowNumber
,Value
,CanBeNull
FROM sampledata

2

What is the "objective"? What result do you want to get?

3

Reorder a data-set based on the number of combined NULLs in a rows columns.

E.g. If table has 15 columns, and 100 rows only 10 of fields have values, 5 fields are NULL. Then 80 rows with 11 populated fields , 4 fields are NULL, and so on

4

Something like this?

Drop Table If Exists #Temp
Create Table #Temp
(
	ID int Not Null Primary Key,
	Col1 int Null,
	Col2 int Null,
	Col3 int Null
)

Insert Into #Temp Values
(1,null, null, 3),
(2,4, 2, 1),
(3,null, 1, null),
(4,12, null, 6),
(5,1, 77, null),
(6,null, null, null)

Select 
	ROW_NUMBER() Over (Order By X.NonNullCount Desc) As Ordinal,
	T.*
From #Temp T
Left Join
(
	Select 
		ID, Count(*) As NonNullCount
	From #Temp
	UnPivot
	(
		C For Cols In (Col1, Col2, Col3)
	) U
	Group By Id
) X On T.ID = X.ID
Order By X.NonNullCount Desc
5

Thanks Andy. Just checking this out.

6

Try something like this:

WITH sampledata (Id, CanBeNull0, CanBeNull1, CanBeNull2, CanBeNull3)
AS
(
SELECT 1, 1, 3, 4, 5
UNION
SELECT 2, 2, 3, 4, NULL
UNION
SELECT 3,  3, NULL, NULL, NULL 
UNION
SELECT 4, NULL, NULL, NULL, NULL 
)
SELECT s.Id
     , s.CanBeNull0
     , s.CanBeNull1
     , s.CanBeNull2
     , s.CanBeNull3
FROM sampledata s
GROUP BY s.Id
       , s.CanBeNull0
       , s.CanBeNull1
       , s.CanBeNull2
       , s.CanBeNull3
ORDER BY Sum(IsNull(s.CanBeNull0,10000)
            +IsNull(s.CanBeNull1,10000)
            +IsNull(s.CanBeNull2,10000)
            +IsNull(s.CanBeNull3,10000)
            ) DESC ;

image

7

This seems to work assuming you don't have any values with "@" characters within the text

select a.* 
from 
sampledata a 
outer apply
(
select  x.x 
from
(
select isnull(b.CanBeNull0, '@') +  isnull(b.CanBeNull1, '@')  + isnull(b.CanBeNull2, '@')  + isnull(b.CanBeNull3 , '@')
from sampledata b
where b.Id = a.Id
for XML path('') 
) x(x)

) x

order by LEN(x.x) - Len(REPLACE(x.x,'@','')) desc