CODEWITHSUNDEEP
sql-server
TuncayCoban
TuncayCoban

Reputation: 7

Multiple OrderID for a single SQL table

Let's imagine a table below, where;

ID  ItemType OrderID Col1 
==  ======== ======= ==== 
 1         1       1 ABCD 
 2         1       2 XYZT  
 3         2       1 BDKL
 4         1       3 XXXX
 5         1       4 TYTY
 6         2       2 ABCD
 7         1       5 XYZZ
 8         3       1 ABCD
 9         3       2 ABCD
10         1       6 XYZT
11         2       3 ABCD

as you see there might be more than one ItemType that comes from another table, and each ItemType has a sequential OrderID that starts from 1 and increases by 1 for every record.

My Question is;

what is the best practice to have a column that keeps the OrderID information correctly?

Upvotes: 0

Views: 386

Answers (2)

gotqn
gotqn

Reputation: 43636

You do not need to do this - it will be difficult to implement and you can face some performance issues if batches of orders are created at the same time. As there is no built -in group by identity or identity over (partition by) you need to get the maximum value for each inserted type - and this should be in transaction and will be blocking others inserted.

So, just have a normal identity column to guarantee uniqueness of each order and use ROW_NUMBER to get the OrderID in incremented way by type in the presentation lair.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Assuming that the ID values would always be increasing, such that a subsequent order's ID value would always be greater than an an earlier order's ID value, we could just use ROW_NUMBER here and not even use a column:

SELECT
    ID,
    ItemType,
    ROW_NUMBER() OVER (PARTITION BY ItemType ORDER BY ID) OrderID,
    Col1
FROM yourTable
ORDER BY
    ID;

screen capture of demo below

Demo

If my assumption of the ID column might not be correct always, then I suggest adding a new timestamp column which records when each order actually happened. Then, use something similar to the above approach, but order based on the order timestamp.

Upvotes: 2

Related Questions