SQL Server Forums
Profile | Register | 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		  New Topic  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
47040 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
47040 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
47040 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  
 New 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.12 seconds. Powered By: Snitz Forums 2000