Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Update records with their occurance with a group

Posted on 2014-10-28
2
Medium Priority
?
163 Views
Last Modified: 2014-10-29
I have a t-SQL problem I can't get my head around.  I have a table of orders with each order having a unique alpha OrderNo.  Each order is associated with a BaseOrder in a many to one relation ship (one baseorder can have multiple orders).

The issue I have is I need to update the table and set a column to indicate the count of the order within the associated base order.  The example below indicates what I am after.  Orders 10001, 10012, 10018 and 1001A are all associated with BaseOrder 10001.  I need to update the SubOrderNumber with 1, 2, 3, 4 respectively as I want them stamped in incremental order based on their alpha sort order.


Example
0
Comment
Question by:canuckconsulting
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 2000 total points
ID: 40409616
Firts try if this select is ginivng you required output :


select orderno, baseorder,  ROW_NUMBER() OVER (PARTITION BY orderno, baseorder ORDER By orderno, baseorder) as SubOrderNumber 
from orders

Open in new window

then try this:

UPDATE t
SET t.SubOrderNumber = t2.SubOrderNumber 
FROM order t
INNER JOIN (
select orderno, baseorder,  ROW_NUMBER() OVER (PARTITION BY orderno, baseorder ORDER By orderno, baseorder) as SubOrderNumber 
from orders
)t2
ON (t.OrderNo= t2.OrderNo and t.BaseOrder =t2.BaseOrder  )
WHERE conditions;

Open in new window

0
 

Author Closing Comment

by:canuckconsulting
ID: 40410996
Fabulous!  I had never heard of the over clause...what a handy tool!

Thanks,

Scott
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question