Selecting with preference in SQL Server

Question

I have a table in SQL Server 2000 with data similar to the following:

ReferenceNumber    ReferenceValue
00001              Not assigned
00002              Not assigned
00002              ABCDE

in which each ReferenceNumber can appear multiple times in the table, either with a ReferenceValue of 'Not assigned' or a true ReferenceValue.

I want to dump the data into a cleaned-up table with only one row per ReferenceNumber and a true ReferenceValue if it exists, or 'Not assigned' if there are no true ReferenceValues.

I can see how to do it with two queries:

SELECT TOP 1 ReferenceNumber, ReferenceValue
INTO clean
FROM duplicates
WHERE ReferenceValue <> 'Not assigned'

INSERT INTO clean(ReferenceNumber, ReferenceValue)
SELECT TOP 1 ReferenceNumber, ReferenceValue
WHERE ReferenceValue = 'Not assigned' 
AND ReferenceNumber NOT IN (SELECT ReferenceNumber FROM clean)

but I'm thinking there must be a better way. Any ideas?

Answer 1

For SQL SERVER 2000, this is probably easiest. First clause = "real" values, second clause where not found in first clause. And an extension of your idea.

SELECT d2.ReferenceNumber, d2.ReferenceValue
FROM duplicates d2
WHERE d2.ReferenceValue <> 'Not assigned'
UNION ALL
SELECT d1.ReferenceNumber, d1.ReferenceValue
FROM duplicates d1
WHERE NOT EXISTS (SELECT *
         FROM duplicates d2
         WHERE d2.ReferenceNumber = d1.ReferenceNumber AND
                 d2.ReferenceValue <> 'Not assigned')

However, what criteria do you want to tie break between "true" reference values? or just pick one?

Answer 2

Something like this:

SELECT 
  ReferenceNumber
, ReferenceValue = ISNULL(MAX(NULLIF(ReferenceValue,'Not assigned')),'Not assigned')
INTO Table1_Clean
FROM Table1
GROUP BY
  ReferenceNumber

MAX() ignores NULLs, so convert whatever you don't want to NULL first, then MAX(), then convert NULLs back to a dummy value.

One pass, in-line, can't get much more efficient.