Filter SELECT statements by row with most populated columns

Question

I have an instruction that SELECTs distinguishes rows from a table with an index of two values

SELECT distinct Reference, Value1, Value2, Value3, Value4 FROM [tblHistory]

Where Reference is an index with a different Project field. For a specific system, this data is inserted into another table, using only the link as an index, because Value1 to Value4 SHOULD always be the same for the same directory, but at about 1/500 this is not so.

In the case where there is a duplicate Reference AND in one or more of the fields Value1-Value4, I need to select the row with the most complete fields Value1-Value4, since they are often NULL. If all instances have the same number of filled columns, I can return the found first row.

Besides using temporary tables and code like

case when Value1 is null then 1 else 0 end 
+ case when Value2 is null then 1 else 0 end 
+ case when Value3 is null then 1 else 0 end
+ case when Value4 is null then 1 else 0 end
as CountOfNulls

Is there a way to filter the data, so I only get the most populated string?

I am running MS SQL Server 2000.

Answer 1

You can take a peek at the Coalesce function, but to be honest, I probably would have done the Case statement, as you already mentioned above.

What reason would you not like to use?

According to the comments, the table is more than just 4 value fields. But the fear is that a temporary table containing the highest score of zeros will be needed.

I feel that the Case solution, potentially embedded in the view, is still a viable and good solution.

Answer 2

, , , , . , , , , , .

, , , .

Answer 3

-- count() will not include NULL, so we can avoid making complex conditions
;
with
sum_cnt
(
    Reference,
    cnt
)
as
(
    select 
        Reference, 
        count(Value1) + count(Value2) + count(Value3) + count(Value4) 
    from 
        tblHistory 
    group by 
        Reference
)
select top 1
    Reference
from
    sum_cnt 
order by
    cnt desc

Answer 4

, , , :

SELECT distinct Reference, Value1, Value2, Value3, Value4

FROM [tblHistory]
WHERE Reference+cast(4-(case when Value1 is null then 1 else 0 end 
+ case when Value2 is null then 1 else 0 end 
+ case when Value3 is null then 1 else 0 end
+ case when Value4 is null then 1 else 0 END) AS varchar) IN (

SELECT myref + CAST(MAX(CountOfNonNulls) AS VARCHAR) FROM
(

SELECT myref, 4-(case when Value1 is null then 1 else 0 end 
+ case when Value2 is null then 1 else 0 end 
+ case when Value3 is null then 1 else 0 end
+ case when Value4 is null then 1 else 0 end)
as CountOfNonNulls

FROM [tblHistory]
)l
GROUP BY Reference
)

, , , , . , "" , Reference CountOfNonNulls , - , CASE , , ( ), 80 , .

, , CountOfNonNulls, Value1-Value4 - , . , , Value1-Value4 "" , .

!