Autonumbering tables - SOLVED - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Autonumbering tables - SOLVED

Author

Topic

fwr1980
Starting Member

3 Posts

Posted - 2012-11-04 : 10:19:28

Hi - Im hoping there is a howto someone can point me to for what is probably a common issue.

I have a list of customers in a parent table.
I have a list of orders in a child table, along with particulars relating to each order such as colour etc.

I would like to be able to restart the numbering of orders for each customer, so I can quickly view how many orders have been ordered by each customer? At the moment, the order list has a primary key that increments for each order, no matter which customer.

I hope this makes sense and Im really grateful for any help.

Thanks
Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 11:30:03

you can put a logic using ROW_NUMBER for filling OrderSeqNo
get maxorderno for customer and
use logic like

MaxOrderSeqNo + ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fwr1980
Starting Member

3 Posts

Posted - 2012-11-04 : 12:02:03

Thanks for the answer. Im working in visual studio - where do I put this piece of code - does it go in the table adapter?

Frank

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 12:17:06

you should be putting this in a sql stored procedure and calling it within visual studio

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

fwr1980
Starting Member

3 Posts

Posted - 2012-11-04 : 12:42:38

Thanks -is there a way to click-thank people on this forum?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-04 : 12:47:41

Nope..You can just post as a reply.

And if think this has solved your problem you can add [Solved] to the post title

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources