Incrementing, from 1, based on another column

muffinbubblemuffinbubble New memberPosts: 2 New member
edited August 20, 2019 10:32AM in SQL Data Generator
Hi, I have a table which simplified is a sales orders lines table, with two columns, a reference to the sales order (this is a FK) and an order line. The order line column should increment from 1 to x but be unique to that sales order ID.

For example:

SalesOrderID    OrderLine
1                          1
1                          2
1                          3
2                          1
2                          2
3                          1

And so on. Currently the order line is totally random and doesn't start from 1. I can set a min/max but it needs to start from the SalesOrderID.

Is this possible? So in effect, the logic for each generated line will ask "Is this a new sales order ID, therefore the OrderLine will generate 1. Further down I randomly generate the same sales order ID, but as a row already exists once with an OrderLine of 1, increment 1 to it, so the OrderLine becomes 2. If a new sales order ID is generated which doesn't exist, we start from 1 again.".

Edit: I did try a generic Python script but it seemed to only allow you to run tests on that column and not get values of others.

Answers

  • Eddie DEddie D Rose Gold 3 Posts: 1,523 Rose Gold 3
    Hi, thank you for your forum.

    From the information you have provided, I cannot identify a method to achieve your goal using the generators provided within the tool.

    One possible solution, would be to create a csv file or SQL Table that can be used as a reference for the example data you wish to generate.

    Many Thanks
    Eddie

    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
  • muffinbubblemuffinbubble New member Posts: 2 New member
    Hi Eddie, I managed to work around it by running a SQL script after generation (grouped on sales order ID and then ROW_NUMBER for the order line.
Sign In or Register to comment.