Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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