0

I have a table representing product groups. Groups are defined by row number and their Type. Hence here we have 2 groups, the group is defined by the Type A, the Type B being components.

Row number Type
0 A
1 B
2 B
3 A
4 B
5 B

With just this data, I need to find back the row number of the last preceeding Type A occurence :

Row number Type Row father
0 A 0
1 B 0
2 B 0
3 A 3
4 B 3
5 B 3

I can't find a way to get this. It's a sort of lag() or first_value() based on condition. Here I have 2 groups of 2 components, but I can have more groups with different sizes. The only thing that defines the group are the row number : every row number under Type A (so with Type B) is a child of the above Type A, until next type A.

Thank you for your help

tmhnr
  • 9
  • 1
  • Please also read https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms – Reporter Mar 31 '22 at 10:31
  • This could be done with rank() in a cte or with a sub-query per line which would be less efficient but not all rdbms accept rank() and cte's, hence the importance of tagging with your rdbms. –  Mar 31 '22 at 11:31
  • This sounds like a usecase for the [HIERARCHY_LEVELED](https://help.sap.com/doc/e4fc5d26a38c4dc28802a3b87cd5eb10/2.0.06/en-US/SAP_HANA_Hierarchy_Developer_Guide_en.pdf) function – Jonas Wilms Mar 31 '22 at 21:37

1 Answers1

2

You can achieve the desired result by using a join in conjunction with a group-by.

Test Data

CREATE TABLE TEST (ROW_NUMBER integer, TYPE varchar(1));
INSERT INTO test  VALUES (0, 'A');
INSERT INTO test  VALUES (1, 'B');
INSERT INTO test  VALUES (2, 'B');
INSERT INTO test  VALUES (3, 'A');
INSERT INTO test  VALUES (4, 'B');
INSERT INTO test  VALUES (5, 'B');

Query

SELECT T.*, MAX(A.ROW_NUMBER) AS ROW_FATHER
FROM TEST T
LEFT JOIN TEST A 
    ON A.TYPE = 'A' AND a.ROW_NUMBER <= T.ROW_NUMBER
GROUP BY T.ROW_NUMBER, T.TYPE
ORDER BY T.ROW_NUMBER

Result

enter image description here

Mathias Kemeter
  • 933
  • 2
  • 11