SQL Server : proper join of data with return of preferred value if present

I’m working on multiple data tables, trying to figure out the best method of joining them. I’m using a primary table (let’s name it Devices) along with additional ones, Settings. All mentioned tables contain DeviceID‘s, which I use as a device identifier.

Thing is, that when there are distinct device records in the Devices table, the Settings table may contain more than one record per device.

The basic device table structure looks like this:

| deviceId | deviceName     |
| -------- | -------------- |
| 0001     | device01       |
| 0002     | device02       |
| 0003     | device03       |

Settings however uses its own records identifier, thus looks like this is many cases:

| recordId | deviceId | settingState   |
| -------- | -------- | -------------- |
| 1        | 0001     | OK             |
| 2        | 0001     | OK             |
| 3        | 0001     | NOK            |
| 4        | 0002     | NOK            |
| 5        | 0002     | N/A            |
| 6        | 0003     | N/A            |

What I’m trying to achieve is this: if a device has a record present with an “OK” setting state (among all other records matching ‘deviceId’), this status is to be returned. If a machine has no “OK” matching record present – then “NOK” is considered a next priority, with “N/A” returned only, when there are no “OK” and “NOK” status records returned.

Example result:

| deviceId | deviceState    |
| -------- | -------------- |
| 0001     | OK             |
| 0002     | NOK            |
| 0003     | N/A            |

So far I succeeded without an issue with JOIN on devices, when there is a 1-1 relation, however I’m stuck on processing more than one records per device.

I tried with CASE but I was unable to properly construct a JOIN to work on DeviceId through separate SELECT statements. Thanks in advance!

Answer

You can use row_number():

select s.*
from (select s.*,
             row_number() over (partition by deviceid
                                order by case settingState when 'OK' then 1 when 'NOK' then 2 when 'N/A' then 3 else 4 end
                               ) as seqnum
      from settings s
     ) s
where seqnum = 1;

A fun method uses aggregation:

select deviceid,
       coalesce(max(case when settingState = 'OK' then settingState end),
                max(case when settingState = 'NOK' then settingState end),
                max(case when settingState = 'N/A' then settingState end),
                max(settingState)
               ) as settingState
from settings s
group by deviceid;