Solved

Return Dynamic Row Number from SQL Select

Posted on 2014-09-15
9
404 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

777 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