Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

SQL to update table with "item numbers"

I am trying to duplicate some functionality I had in an Access app in a SQL Server environment via asp.net web forms.

My problem is with numbering items that are assigned to a Purchase Order.  After creating a new Purchase Order, users are directed to a screen where they can add items to their "cart".  The cart is based on a temporary table created via stored procedure with a parameter for the table name, which is the username plus date plus "TempItems".  

When they click a "submit" button, I want to sort the items they've added in the temp table and populate an "itemnumber" field with a sequential number - for each record, it's the count of items with itemdescriptions that are <= each record's itemdescription, if that makes any sense.

I used DCount in my Access app, and the Append Query I ran had a field with alias "Sequence" which was:  Sequence: Dcount("itemid", "tempitems", "itemdescription <=itemdescription).  This method worked very well.  

How do I do it in SQL Server??
0
Alastair_White
Asked:
Alastair_White
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hi. In the tags you have SQL 2005, is that the correct version you are using? If so, introduced in SQL 2005 is windows function ROW_NUMBER() which combined with OVER() analytical clause will do what you need.

Some thing like:
ROW_NUMBER OVER(ORDER BY ItemDescription) AS "Sequence"

Open in new window


0
 
Kevin CrossChief Technology OfficerCommented:
For some more information on it, especially since you did this in Access, please see my article which compares the ranking options between SQL and Access; therefore, it should be a worthwhile read.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
0
 
Alastair_WhiteAuthor Commented:
Yes, I am using SQL Server 2005, and I have now used the row_number() / over() combo to get my "item numbers" as desired.  

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now