How to filter rows in SQL EAV model?

Question

I got a EAV SQL table which contains a list of preferences used for the display of a GUI. Each preference is represented by a row in the table.

I need to filter these rows to have only one row per different ColumnName/key column combination. If the ID_UserPersonal column is not NULL, it means that the row represents a user-specific preference which must have precedence over the default ones (when ID_UserPersonal is NULL). In that case, the default preference must be filtered out and the user-defined one must be kept for that ColumnName/key column combination.

Answer 1

You could rank rows by ID_Personal putting NULLs after non-NULL values, then select the rows where ranking values are 1, something like this:

;
WITH ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (
      PARTITION BY ID_Role, Section, GridName, ColumnName, [key]
      ORDER BY CASE WHEN ID_Personal IS NULL THEN 1 ELSE 0 END, DateCreated
    )
  FROM AnEAVTable
)
SELECT *  /* or specify your columns explicitly, which is even better */
FROM ranked
WHERE rnk = 1

Please pay attention to the PARTITION BY clause of the ROW_NUMBER() function in my example, in case I added too much (or, perhaps, too few) columns there.

References:

• Ranking Functions (Transact-SQL)

• ROW_NUMBER (Transact-SQL)