Solved

Return Dynamic Row Number from SQL Select

Posted on 2014-09-15
9
399 Views
Last Modified: 2014-09-22
Hello experts,

We have 2 tables - here is a very simplified view.

Orders - Master Table
  OrderID     INT  PK
  ORDERDATE     DATETIME
  PONUM     INT
  SUPPLIERID     INT

TrackNums - Stores shipment details for suppliers sending product to us
TRACKNUMID     INT PK
ORDERID   INT
CREATEDATE  DATETIME
SHIPPERID   INT
TRACKNUM  VARCHAR(20)
QTY   INT
RECEIVED   DATETIME

I'm writing a stored procedure to get all orders that have tracking numbers (shipments) associated with them

Some Orders can have more than one inbound shipment from suppliers.  So, for example Order # 101 may have 3 shipments on it.  The 1st and 3rd shipment may have arrived fine, but the 2nd shipment may be delayed.  

In my stored procedure results when I make reference to that shipment, I'd like to identify that it was the 2nd shipment entered against that order ID.  I've got some code working with RANK in a new query, but it is not working properly when I try to integrate it into the stored proc.

For example, below, I"m trying to show the shipment # based on looking at all shipments for the given Order ID, sorting them ascending order by TrackNumID and determining which shipment # it was for that Order ID (again this is simplified so not all components of the query are shown):

SELECT O.ORDERID, O.PONUM, T.TRACKNUM, T.RECEIVED, SHIPMENT#
from Orders O
LEFT JOIN TRACKNUMS T ON T.ORDERID = O.ORDERID
WHERE T.TRACKNUM IS NOT NULL

Any thoughts?  Thanks!
0
Comment
Question by:dpmoney
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40324346
Can you please provide sample data and desired result. Your simplified query does not really describe the problem you are facing
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40324577
I would guess ROW_NUMBER() is the function you want instead of RANK() or DENSE_RANK()

SELECT
      O.ORDERID
    , O.PONUM
    , T.TRACKNUM
    , T.RECEIVED
    , ROW_NUMBER() OVER(PARTITION BY O.ORDERID
                        ORDER BY T.CREATEDATE ASC) AS SHIPMENT#
FROM Orders O
      INNER JOIN TRACKNUMS T
                  ON O.ORDERID = T.ORDERID
;

Open in new window

Please note that using an INNER JOIN is more efficient than a left join then applying a where condition on that left joined table - which produces the effect on an inner join (sometimes called "implicit inner join")
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40326945
Please note that using an INNER JOIN is more efficient than a left join then applying a where condition on that left joined table
That is a great way of phrasing that.  I think I may plagiarize that sentence.  :)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40326971
@Anthony, Please feel free to do so, although "borrow" or "re-use" may be more acceptable behaviours :)
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40326987
"Good artists copy, great artists steal." - Pablo Picasso
:)
0
 

Author Comment

by:dpmoney
ID: 40328091
Thanks - I'll give this a try when back in the office on Thursday.  If it does not work, I'll provide sample data to further illustrate the question.  Will follow-up ASAP.
0
 

Author Comment

by:dpmoney
ID: 40337277
@PortletPaul

The function you suggested gets me closer to the solution, but not completely.  The issue is my query only returns undelivered shipments.  As a result, the row number function is only assigning a unique row number for the returned records whereas I need it to determine which shipment it was a for a given order (taking into account all shipments that have been received).  Here is an easy example.

Order # 101
Shipment #1 = 100 PCs, RECEIVED
Shipment #2 = 200 PCs, UNDELIVERED
Shipment #3 = 300 PCs, RECEIVED
Shipment #4 = 400 PCs, UNDELIVERED

My query will return 2 tracking # records (shipment 2 and 4) because they are not received.  Using the Row Number function, Shipment #2 is being labeled as #1, and Shipment #4 is being labeled as #2 because they are the only 2 records returned.  In reality, taking all 4 shipments into account for that Order, they are really shipment #2 and shipment #4.

I tried doing a left join to a derived table that selects all records from the TrackNums table and gives each a row number, but it basically counts from 1 to 49,000.  I need it to start at a new number each time the order ID changes.

For example, I'd need it to show:

OrderNum, Shipment #, TrackNum
101, 1, 1Z999
101, 2, 1Z888
101, 3, 1Z777
101, 4, 1Z666
102, 1, 1X555
102, 2, 1X444
102, 3, 1X333
103, 1, 1W444
104, 1, 1Y888

See what I mean?  The dynamic row number assigns a dynamic shipment # to each shipment for a given order # and then  starts the count fresh for each order #.  Is this possible?

Thanks!
0
 

Author Comment

by:dpmoney
ID: 40337321
I think I figured out how to go the rest of the way - resetting the count each time I hit a new Order #.  

I'll simply do the LEFT JOIN as noted above to this derived table below and pick up the ShipmentNum by joining on the TrackNumID Primary Key:

SELECT TrackNumID, TrackNum, CreateDate, OrderID,
ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY TrackNumID) AS ShipmentNum
FROM TrackNums
ORDER BY OrderID, ShipmentNum

Points awarded to PortletPaul for giving me the direction I needed.  If you have any additional info or a more efficient approach, I'd love to hear it.  Thanks!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40337959
Correct: you need "partition by" as well as "order by"
(it is good that you recognize this, but for the record I did supply that at line 6 ID: 40324577)

It is unlikely you will find a more efficient approach for this part of your overall query.

Cheers, Paul
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now