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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Autonumbering tables - SOLVED
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fwr1980
Starting Member

United Kingdom
3 Posts

Posted - 11/04/2012 :  10:19:28  Show Profile  Reply with Quote
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

Edited by - fwr1980 on 11/04/2012 12:54:57

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/04/2012 :  11:30:03  Show Profile  Reply with Quote
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/

Go to Top of Page

fwr1980
Starting Member

United Kingdom
3 Posts

Posted - 11/04/2012 :  12:02:03  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/04/2012 :  12:17:06  Show Profile  Reply with Quote
you should be putting this in a sql stored procedure and calling it within visual studio

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

Go to Top of Page

fwr1980
Starting Member

United Kingdom
3 Posts

Posted - 11/04/2012 :  12:42:38  Show Profile  Reply with Quote
Thanks -is there a way to click-thank people on this forum?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 11/04/2012 :  12:47:41  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000