Solved

Return Dynamic Row Number from SQL Select

Posted on 2014-09-15
9
397 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@Anthony, Please feel free to do so, although "borrow" or "re-use" may be more acceptable behaviours :)
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 75

Expert Comment

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

Author Comment

by:dpmoney
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

744 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

15 Experts available now in Live!

Get 1:1 Help Now