My Question:
I'm using MySQL and I have two columns called column1 and column2 of equal length. My goal is to find out for each value in column1 (if it were ordered) where (after which row) it should be inserted into column2 to preserve the order of column2 (if it were ordered). As an example, suppose that column1 and column2 have the following values:
column1 = [1, 2, 2, 4]
column2 = [0, 1, 1, 3]
In this case the result that I would want to find would be
result = [1, 3, 3, 4]
because the first value of column1 would have to be inserted in column2 behind row 1, the second and the third values of column1 would have to be inserted in column2 behind row 3 and the fourth value of column1 would have to be inserted in column2 behind row 4.
My approach:
What I can do is create a new column called values that is the concatenation of column1 to column2 and create a new column called col that indicates whether the row originally came from column1 or column2. If I order the results based on both new columns I get the following result:
values col
0 col2
1 col1
1 col2
1 col2
2 col1
2 col1
3 col2
4 col1
If I now run the following query on these two columns
SELECT values, col, SUM(CASE WHEN col = 'col2' THEN 1 ELSE 0 END) OVER(ORDER BY values, col) AS row
FROM new_table
I get the following
values col row
0 col2 1
1 col1 1
1 col2 2
1 col2 3
2 col1 3
2 col1 3
3 col2 4
4 col1 4
Selecting from this result the rows that have col = col1 will give me what I am looking for.
I'm sure that there's a better way to do this!