June 16, 2008 at 3:25 pm
I am only posting this because I have tried searching and have not been able to find a solution that meets my needs.
I have a table that contains several elements:
OrderNumber int,
LineItemNumber int,
LineItemDesc char,
LineItemCount int
I have a procedure that steps through and removes duplicates based on 'OrderNumber' and 'LineItemDesc'. The 'LineItemCount' is populated with the number of total line items after duplication removal. What I want to do is renumber the 'LineItemNumber' so that it omits the removed line items. A small example is listed below. In my situation, both of the matching line items need to be omitted.
Original Table:
OrderNumber, LineItemNumber, LineItemDesc
123, 1, eggs
123, 2, bread
123, 3, milk
123, 4, eggs
456, 1, paper
456, 2, pencil
456, 3, pencil
456, 4, pen
456, 5, eraser
456, 6, pencil
Processed Table:
OrderNumber, LineItemNumber, LineItemDesc, LineItemCount
123, 2, bread, 2
123, 3, milk, 2
456, 1, paper, 3
456, 4, pen, 3
456, 5, eraser, 3
Desired Outcome:
123, 1, bread, 2
123, 2, milk, 2
456, 1, paper, 3
456, 2, pen, 3
456, 3, eraser, 3
I have read that cursors may be able to solve my issue, but I have also read that they are slow. Is there a method that could accomplish this task without cursors?
Thank you for any help in advance! This particular problem is really bothering me.
John
June 16, 2008 at 4:44 pm
This is the most straightforward way, though not the fastest.
Insert into ProcessedTable(OrderNumber, LineItemNumber, LineItemDesc)
Select OrderNumber, LineItemNumber, MAX(LineItemDesc)
From OriginalTable
Group By OrderNumber, LineItemNumber
Having COUNT(*) = 1
Update P
Set LineItemNumber = (Select Count(*) From ProcessedTable P2
Where P2.OrderNumber=P.OrderNumber
And P2.LineItemNumber <= P.LineItemNumber)
, LineItemCount = (Select Count(*) From ProcessedTable P2
Where P2.OrderNumber=P.OrderNumber
Group By P2.OrderNumber)
From ProcessedTable P
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 18, 2008 at 10:26 am
Thank you! Even if it is not the most efficient code, it gets the job done. It worked perfectly for my situation. I would be interested in knowing if there was a better solution to this problem but we are moving to SQL 2005 soon so I should just be able to use the RANK() function. Thanks again.
John
June 18, 2008 at 4:45 pm
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply