plankton - 7 months ago 49

SQL Question

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

I have a

`Sybase`

`unique ID`

`ID`

`CASE WHEN`

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
... | ... | ...
```

Source (Stackoverflow)