Solved

Return Dynamic Row Number from SQL Select

Posted on 2014-09-15
9
420 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 25

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

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database maintenance 36 98
Many to one in one row 2 35
Delete old Sharepoint backups 2 11
Need age at date of document 3 8
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

713 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