SQL Selecting Values Based on Hierarchically-Ordered List-CodePudding

I have a table (at the bottom) with base values A1, A2, A3, A4 ... An, and overriding values B1, B3, and C2, C3, C4 and D1, D4.

The data could be visualized like this:

A1  A2  A3  A4
B1      B3 
C1  C2      C4
    D2  D3

With SQLite and the table below, if I have an ordered list of Letters A, B, C, and D in any order and length, I'd want the table to return those values contained in the highest level, and then fill in the missing holes from the lower levels.

Example: [D > B > A] (omitting C) should grab all the values 1-4 from "D": [D2, D3], then get missing values for 1 and 4 from B [B1] and finally the missin 4 from A [A4] to yield [B1, D2, D3, A4]. but [C > B > A] would return [C1, C2, B3, C4]. The order of the returne

Input          Output
C > B > A      [C1, C2, B3, C4]
B > A          [B1, A2, B3, A4]
A > B > C > D  [A1, A2, A3, A4]
D > C > A      [C1, D2, D3, C4]
B > D          [B1, D2, D3] or [B1, D2, D3, null]
B              [B1, B3] or [B1, null, N3, null]

So in my table I have my data arranged like this. In this table Number Letter is a composite key. Any combination of these rows will return either zero or one row. The rows are not guaranteed to be in any particular order: the example table is for ease of explanation. The results do not need to be in any certain order.

ID (PK)	Number	Letter	Value
1	1	A	a
2	2	A	b
3	3	A	c
4	4	A	d
5	1	B	e
6	3	B	f
7	1	C	g
8	2	C	h
9	4	C	i
10	2	D	j
11	3	D	k

CodePudding user response：

You can use row_number. Params CTE specifies the input hierarchy, C > B > A for example

with params(Letter, Pos) as (
  select 'A', 3 union all
  select 'B', 2 union all
  select 'C', 1 union all
  select 'D', -1 
)
select *
from(
 select t.*, row_number() over(partition by c.Number order by p.Pos) rn
 from mytable c
 join params p on p.Letter = c.Letter
   and p.Pos > 0
)t 
where rn = 1;

Returns

ID  Number  Letter  Value   rn
7   1   C   g   1
8   2   C   h   1
6   3   B   f   1
9   4   C   i   1

CodePudding user response：

Using exists with a subquery:

with inp(letter) as ( 
  select "D"
  union all
  select "B"
  union all
  select "A"
),
cte as (
  select row_number() over (order by 1) r, t1.number, t1.letter 
  from inp t join test t1 on t.letter = t1.letter
)
select c.number, c.letter from cte c where not exists (select 1 from cte c1 where c1.r < c.r and c.number = c1.number)
order by c.number