Sorting column A based on a column B which contains previous values from column A

Question

I'd like to sort column A based on a column B which contains previous values from column A.

This is what I have:

+----+----------+----------+
| ID |    A     |    B     |
+----+----------+----------+
|  1 | 17209061 |          |
|  2 | 53199491 | 51249612 |
|  3 | 61249612 | 17209061 |
|  4 | 51249612 | 61249612 |
+----+----------+----------+

And this is what I'd like to have:

+----+----------+----------+----------+
| ID |    A     |    B     | Sort_seq |
+----+----------+----------+----------+
|  1 | 17209061 |          |        1 |
|  3 | 61249612 | 17209061 |        2 |
|  4 | 51249612 | 61249612 |        3 |
|  2 | 53199491 | 51249612 |        4 |
+----+----------+----------+----------+

I'm sure there's an easy way to do this. Do you have any ideas?

Thank you!

It's how it should be. ID from the first table has nothing to do with sorting sequence. I need to sort it the right way to get last ID, which should be 2. — Dontik, May 17 '19 at 10:33
. . SQL tables represent *unordered* sets. The `id` is needed to define what "previous value" means. — Gordon Linoff, May 17 '19 at 10:42

score 0 · Answer 1 · answered May 17 '19 at 10:27

Just use lag() in order by:

order by lag(a) over (order by id) nulls first

If you want a column, then:

select t.id, t.a, t.prev_a,
       row_number() over (order by prev_a nulls first)
from (select t.*, lag(a) over (order by id) as prev_a
      from t
     ) t;

Sorting column A based on a column B which contains previous values from column A

1 Answers1