0

Thanks to this good forum, got a number of solutions to my past queries, thanks to Google too. This is my first post in the forum although.

I have a table order with columns pin, orderPath, quantity. There are 5000 data in the table already. Now, I want to add an identy column, say OrderID, but with order by column pin.

Using the following query helps but not in the order as required.

ALTER TABLE order
ADD OrderID numeric(6,0) identity

Here's what I would like to see:

Pin--OrderPath--Quantity--OrderID 

11   xyz/pop    200       1 
22   kl/pod     100       2 
33   djh/dd     200       3  
44   dj/po      300       4
Manoj Paul
  • 1
  • 1

2 Answers2

0
ALTER TABLE orderADD 
ADD Order_ID datatype
gasroot
  • 515
  • 3
  • 15
0

If any of your logic requires the data to be returned or displayed in a certain order, don't rely on the database. Explicitly put an order by clause in your queries to ensure consistency.

SELECT pin, OrderPath, Quantity, OrderID
ORDER BY pin

If you need data to be stored in a particular order, it's recommended to us a clustered index on the column you want orderd. Clustered indexes keep data in order, making for faster retrieval, but keep in mind it does add cost to inserts.

create unique clustered index pin_idx on ORDER (pin)
Mike Gardner
  • 6,611
  • 5
  • 24
  • 34
  • Nor did I, that's why I went with both Indexes and Order by. – Mike Gardner May 14 '13 at 13:34
  • In other way, the "pin" column is also unique in its own, no duplicate rows. What I required is that when I add the identity column OrderID, it should be something like below: – Manoj Paul May 14 '13 at 15:03
  • Pin OrderPath Quantity OrderID – Manoj Paul May 14 '13 at 15:04
  • It should have a sequence OrderId and must be order by Pin – Manoj Paul May 14 '13 at 15:07
  • Sorry for the large number of comments, I need to check how the format works in the forums. – Manoj Paul May 14 '13 at 15:08
  • You should add information like this to your question, instead of putting it it comments, but I understand not all function are available until you have < 1 points. I've change the syntax of the select to match what you are looking for, as well as editing your question to add this information in. – Mike Gardner May 14 '13 at 15:50