CODEWITHSUNDEEP
sqlsql-server
BG3204
BG3204

Reputation: 3

SQL Parent Child and Inheritance

I am using SQL Server 2016 and I'm hoping someone can help me crack this nut.

I have a table that holds a relationship between identical records, and their properties. This is a sample table.

CREATE TABLE [Family] ([Id] Int NOT NULL, [Relation] VARCHAR(12), [Level] TinyInt,  [Shoes] VARCHAR(12), [Shirt] VARCHAR(12), [Coat] VARCHAR(12))

INSERT INTO [Family] ([Id], [Relation], [Level], [Shoes], [Shirt], [Coat])
   VALUES (1, 'Grandparent', 1, 'Blue', 'Brown', 'Green'),
          (1, 'Parent',2, 'Red', NULL, NULL),
          (1, 'Child', 3, NULL, 'Yellow', NULL),
          (2, 'Grandparent', 1, 'Purple', 'Grey', 'Blue'),
          (2, 'Parent',2, NULL, 'Brown', 'Green')
          (2, 'Child', 3, NULL, NULL, 'Yellow');

The key fields are Id and a Level. The rule is that I want to be able to select and return one row per id where the property returned it the one with the highest level that is not null. i.e if the value is null it is inherited from the next lower level.

I hope I explained that right, so the results would look like this:

Id          Shoes  Shirt  Coat
----------- ------ ------ ------
1           Red    Yellow Green
2           Purple Brown  Yellow

I have tried a few ways with ISNULL and functions, but they end up a mess and are slow. Of course what I have posted here is a simple example, the real table would have 8-10 levels and 20+ columns of properties, and I need it to perform over a large dataset.

I have had a hunt around and not found anything really matches, thank you

Upvotes: 0

Views: 580

Answers (2)

LukStorms
LukStorms

Reputation: 29647

I just had to try this.
Apparently a DISTINCT is processed after a window function like FIRST_VALUE.

SELECT DISTINCT Id, 
 FIRST_VALUE(Shoes) OVER (PARTITION BY Id ORDER BY IIF(Shoes is null,0,Level) DESC) AS Shoes,
 FIRST_VALUE(Shirt) OVER (PARTITION BY Id ORDER BY IIF(Shirt is null,0,Level) DESC) AS Shirt,
 FIRST_VALUE(Coat)  OVER (PARTITION BY Id ORDER BY IIF(Coat is null,0,Level) DESC) AS Coat
FROM Family;

Result:

Id  Shoes   Shirt   Coat

1   Red     Yellow  Green
2   Purple  Brown   Yellow

A test on rextester here

Upvotes: 1

LordHolg
LordHolg

Reputation: 11

The first thing that came across my mind was the next idea:

select f.Id,shoes.Shoes,shirt.Shirt,coat.Coat from [Family] f
left join (select top 1 with ties Id,Shoes from [Family] order by ROW_NUMBER() over (partition by Id order by case when Shoes is not null then [Level] else null end) desc) shoes
    on shoes.Id = f.Id
left join (select top 1 with ties Id,Shirt from [Family] order by ROW_NUMBER() over (partition by Id order by case when Shirt is not null then [Level] else null end) desc) shirt
    on shirt.Id = f.Id
left join (select top 1 with ties Id,Coat from [Family] order by ROW_NUMBER() over (partition by Id order by case when Coat is not null then [Level] else null end) desc) coat
    on coat.Id = f.Id
group by f.Id,shoes.Shoes,shirt.Shirt,coat.Coat
order by f.Id

Upvotes: 0

Related Questions