How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?

by Louis Somers   Last Updated August 14, 2019 13:06 PM

I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc...

These are stored in a table with a Hirarchy-ID to sort on, like this:

HIERARCHY | SETTING | OPTION | PREFERENCE | COLOR |
----------+---------+--------+------------+-------+
100       | NULL    | 1      | NULL       | NULL  |
10        | NULL    | 2      | square     |       |
1         | 0       | 3      | rounded    | green |

The result I'm after is for each column, the first not-null value (and it would be great if I can also squash a NULLIF(COLOR, '') in as well). In this example the result would be:

SETTING | OPTION | PREFERENCE | COLOR |
--------+--------+------------+-------+
0       | 1      | square     | green |

Currently I'm doing a separate query for each column but I'm hoping that this can be achieved in a more elegant way?

I'm working with MS SQL Server (and need to support version 2005).

Tags : coalesce


Related Questions



Merging continous date range using TSQL

Updated April 19, 2018 16:06 PM

Ignore the joins without any result in SQL Server

Updated November 23, 2017 09:06 AM