plankton plankton - 1 month ago 13
SQL Question

SQL: Assigning a 2 row unique ID for every 2 rows of data

I think I worded the title a little awkwardly but here is my issue:

I have a

Sybase
table like below where every 2 rows needs to have a
unique ID
that's separate from every other 2 rows in the table. So rows 1 and 2 need an
ID
that is different from rows 3 and 4 and the rest of the table. The "Symbol" can repeat. There is always a "B" and an "S" tied to each other but no current ID to connect them. The ExecID and OrderID is not always truly sequential (ie 010001, 010002) but will be in order. I've tried a few variations of
CASE WHEN
but I can't think of anything that will do the trick.

What is the best way to do this?

Symbol | Sell-Buy | ExecId | OrderId | RowNum |
---------------------------------------------------
W | B | 64240624 | 101773407 | 1 |
W | S | 64240625 | 101773408 | 2 |
XM | B | 64240626 | 101773409 | 3 |
XM | S | 64240627 | 101773410 | 4 |
GV | B | 64240628 | 101773411 | 5 |
GV | S | 64240629 | 101773412 | 6 |

....

W | B | 64240679 | 101773455 | 49 |
W | S | 64240680 | 101773456 | 50 |


Goal of the output:

Symbol | Sell-Buy | ExecId | OrderId | RowNum | 2RowId |
------------------------------------------------------------
W | B | 64240624 | 101773407 | 1 | 000001 |
W | S | 64240625 | 101773408 | 2 | 000001 |
XM | B | 64240626 | 101773409 | 3 | 000002 |
XM | S | 64240627 | 101773410 | 4 | 000002 |

....

W | B | 64240679 | 101773455 | 49 | 000025 |
W | S | 64240680 | 101773456 | 50 | 000025 |

Answer

One trick you could use here would be to take the ceiling of the current row number divided by 2:

UPDATE yourTable
SET 2RowId = CEILING(RowNum / 2)

Here is a table showing how the math should work out:

RowNum | RowNum/2 | ceiling(RowNum/2)
1      | 0.5      | 1
2      | 1        | 1
3      | 1.5      | 2
4      | 2        | 2
...    | ...      | ...