I am writing you because I can't find any solution to my problem, and I don't even know if there is a way to do it. I'm working in SQL (MonetDB).
I have a table like this :
| username | Field1 | Field2 |
|---|---|---|
| AAA | NULL | 1 |
| AAA | type1 | 12 |
| AAA | type2 | 21 |
| AAA | type3 | 1 |
| BBB | null | 6 |
| BBB | type1 | 2 |
| BBB | type4 | 3 |
| CCC | type1 | 23 |
| CCC | type3 | 352 |
| CCC | type4 | 12 |
This table is already sorted by username (with nulls first), then field1, and i don't have any replicates on these two colums together.
I need to update another column. When the row above have the same username and field1 isn't null, then new field3 should be the CONCAT(Field1, Field2) Then for the next row, if the username is the same but Field1 isn't null again and different from the previous one, then Field3 should be CONCAT(previous Field3, Field1, Field2).
I'm trying to adapt a treatement who do it, row by row, and retain the last value saved, that's why i'm struggling about doing it in a relational db.
My final results should be :
| username | Field1 | Field2 | Field 3 |
|---|---|---|---|
| AAA | NULL | 1 | |
| AAA | type1 | 12 | type1 : 12 |
| AAA | type2 | 21 | type1 : 12 type 2 : 21 |
| AAA | type3 | 1 | type1 : 12 type 2 : 21 type3: 1 |
| BBB | null | 6 | |
| BBB | type1 | 2 | type1:2 |
| BBB | type4 | 3 | type1:2 type4:3 |
| CCC | type1 | 23 | type1 : 23 |
| CCC | type3 | 352 | type1 : 23 type 3:352 |
| CCC | type4 | 12 | type1 : 23 type 3:352 type 4:12 |
The final goal is afterwards to select username and the longest field3 by username. Hope I've been clear in what I need to do, tell me if you need more explanations. Thank you for your help !
If you want to use information in a previous row, you have to look into "window functions". They exist in a number of SQL databases, including MonetDB: https://www.monetdb.org/Documentation/SQLReference/FunctionsAndOperators/WindowFunctions
The condition
WHERE LAG(username) OVER (ORDER BY username, field1) = username
will check if the previous line's username (with the order given by username, field) is equal to the current line's username.