Return Dynamic Row Number from SQL Select
Posted on 2014-09-15
We have 2 tables - here is a very simplified view.
Orders - Master Table
OrderID INT PK
TrackNums - Stores shipment details for suppliers sending product to us
TRACKNUMID INT PK
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!