0

a example with real data:

CustomerId      Scope       Key                             Value
1               Customer    SelfPasswordReset_MaxAttempts   5
2               Customer    SelfPasswordReset_MaxAttempts   10
3               Customer    SelfPasswordReset_MaxAttempts   20
NULL            Platform    PlatformGUID                    c20414f6-3bd2-4d37-ac74-152b0efdb582
...

assume we further have a CustomerParant table

CustomerId  ParentId    Level
5           5           0
5           1           1
4           4           0
4           2           1
4           1           2
3           3           0
3           2           1
3           1           2
2           2           0
2           1           1
1           1           0

listing the parents of every customer and how much iterations you need to traverse through the tree to arrive there. means 3's parent is 2, 2's parent is 1 etc...

Now i want to build a query that returns a result like this:

CustomerId  SelfPasswordReset_MaxAttemps    PlatformGUID ... 
5           5                   c20414f6-3bd2-4d37-ac74-152b0efdb582
4           10                  c20414f6-3bd2-4d37-ac74-152b0efdb582
3           20                  c20414f6-3bd2-4d37-ac74-152b0efdb582
2           10                  c20414f6-3bd2-4d37-ac74-152b0efdb582
1           5                   c20414f6-3bd2-4d37-ac74-152b0efdb582

Special Note: Not every customer has his level defined. as you see i added customer 4 and 5 who don't have data a parameter defined of their own. they will inherit from their parent instead

Kyoshiro Kokujou Obscuritas
  • 83
  • 2
  • 16
  • In the new SQL Server 2022, as well as SQL Azure, you can do `FIRST_VALUE(...) IGNORE NULLS`, under older versions it's more complicated and requires multiple levels of nesting window functions, alternatively self-joined subqueries. – Charlieface Aug 08 '22 at 10:29
  • that is very good to know! you could even mark it as an answer if it works. however for my particular scenario we can't use it since our customers demand we continue using comaptibility level 120 or so... but still it might be helpful for others! a related question: how do you sort a query before grouping? because it seems that order by group by is not supported... >.> – Kyoshiro Kokujou Obscuritas Aug 09 '22 at 06:48
  • You can't as aggregate functions are not designed to have ordering in them. That's why I said you need to nest window functions. Even in 2022 you would need at least one – Charlieface Aug 09 '22 at 08:11
  • okay this is really bad... but thanks for clarifying – Kyoshiro Kokujou Obscuritas Aug 09 '22 at 13:10
  • Can you share proper sample data, as well expected results? Your current example is unclear – Charlieface Aug 09 '22 at 13:24
  • i added a real world example with anonymized data. – Kyoshiro Kokujou Obscuritas Aug 09 '22 at 13:56

1 Answers1

0

Generally SQL Coalesce function that returns a first defined which is non null value from argument list like

Coalesce (NULL,1,2) // this will return 1

Coalesce (NULL,'abc','xyz') // this will return abc

Akshay Phadke
  • 81
  • 5