Go Premium for a chance to win a PS4. Enter to Win

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

Return Dynamic Row Number from SQL Select

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
dpmoney
Asked:
dpmoney
  • 3
  • 3
  • 2
  • +1
1 Solution
 
chaauCommented:
Can you please provide sample data and desired result. Your simplified query does not really describe the problem you are facing
0
 
PortletPaulCommented:
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
 
Anthony PerkinsCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
PortletPaulCommented:
@Anthony, Please feel free to do so, although "borrow" or "re-use" may be more acceptable behaviours :)
0
 
Anthony PerkinsCommented:
"Good artists copy, great artists steal." - Pablo Picasso
:)
0
 
dpmoneyAuthor Commented:
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
 
dpmoneyAuthor Commented:
@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
 
dpmoneyAuthor Commented:
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
 
PortletPaulCommented:
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

Technology Partners: 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!

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