SQL return only 1 in SubQuery.

I have a requirement to add to a larger query the next release date for our orders.

The release table has the following.
po_line_uid    release_date   release_qty   qty_received  row_status_flag  
125                    4/4/2015           9                     6                        704
125                    5/4/2015           9                     0                        704
125                    6/4/2015           9                     0                        704
125                    7/4/2015           9                     0                        704
125                    8/4/2015           9                     0                        704


The out put I'm looking for is.

PoNo    date_due   inv_mast_uid   qty_remaining   qty_received  release_qty   release_date
415         4/6/15        125654                 3                               6                9                  4/4/2015

Using the below Query.  I get a line for each release date.

If I try with TOP 1 in the subquery it does not return a release date.  

I've tried with Group and MAX/MIN and it pulls numbers from the other rows.   What I want is the top row only for that line.

SELECT        po_line.po_no, po_line.date_due, po_line.inv_mast_uid, derivedtbl_1.qty_remaining, derivedtbl_1.qty_received, derivedtbl_1.release_qty, 
                         derivedtbl_1.release_date
FROM            po_line LEFT OUTER JOIN
                             (SELECT  release_date, release_qty, qty_received, release_qty - qty_received AS qty_remaining, po_line_uid
                               FROM            po_line_schedule
                               WHERE        (row_status_flag = 704)    ) AS derivedtbl_1 ON po_line.po_line_uid = derivedtbl_1.po_line_uid 

where  po_no = 415 and complete <> 'Y'

Open in new window

LVL 4
edwardqAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
I believe OUTER APPLY with TOP (1) might be more efficient here than ROW_NUMBER():


SELECT        po_line.po_no, po_line.date_due, po_line.inv_mast_uid,
                           derivedtbl_1.qty_remaining, derivedtbl_1.qty_received, derivedtbl_1.release_qty,
                           derivedtbl_1.release_date
FROM            po_line OUTER APPLY
                             (SELECT TOP (1) release_date, release_qty, qty_received, release_qty - qty_received AS qty_remaining, po_line_uid
                               FROM            po_line_schedule
                               WHERE        (row_status_flag = 704)    
                                 AND        po_line_uid = po.po_line_uid
                               ORDER BY release_date
                                 ) AS derivedtbl_1


where  po_no = 415 and complete <> 'Y'
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
SELECT        po_line.po_no, po_line.date_due, po_line.inv_mast_uid, derivedtbl_1.qty_remaining, derivedtbl_1.qty_received, derivedtbl_1.release_qty,
                         derivedtbl_1.release_date
FROM            po_line LEFT OUTER JOIN
                             (SELECT  release_date, release_qty, qty_received, release_qty - qty_received AS qty_remaining, po_line_uid
                               FROM            po_line_schedule
                               WHERE        (row_status_flag = 704) and release_date is not null    ) AS derivedtbl_1 ON po_line.po_line_uid = derivedtbl_1.po_line_uid

where  po_no = 415 and complete <> 'Y'
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
If the above doesn't work you'll need to do a row_number:

;with derivedtbl_1 as (
SELECT  release_date, release_qty, qty_received, release_qty - qty_received AS qty_remaining, po_line_uid
row_number() over (Partition By po_line_uid order by Release_date) RN
                               FROM            po_line_schedule
                               WHERE        (row_status_flag = 704) and release_date is not null    ) 
)

SELECT        po_line.po_no, po_line.date_due, po_line.inv_mast_uid, derivedtbl_1.qty_remaining, derivedtbl_1.qty_received, derivedtbl_1.release_qty, 
                         derivedtbl_1.release_date
FROM            po_line LEFT OUTER JOIN
LEFT OUTER JOIN derivedtbl_1 ON po_line.po_line_uid = derivedtbl_1.po_line_uid 
where  po_no = 415 and complete <> 'Y' and derivedtbl_1.RN = 1

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
edwardqAuthor Commented:
That will still produce the same output of the item with each release date.

This is what that is returning.
PoNo    date_due   inv_mast_uid   qty_remaining   qty_received  release_qty   release_date
415         4/6/15        125654                 3                               6                9                  4/4/2015
415         4/6/15        125654                 9                               0                9                  5/4/2015
415         4/6/15        125654                 9                               0                9                  6/4/2015
415         4/6/15        125654                 9                               0                9                  7/4/2015
415         4/6/15        125654                 9                               0                9                  8/4/2015


The out put I'm looking for is only 1 line. .

PoNo    date_due   inv_mast_uid   qty_remaining   qty_received  release_qty   release_date
415         4/6/15        125654                 3                               6                9                  4/4/2015
0
 
edwardqAuthor Commented:
Bare in mind that the release table has other Lines in it that doesn't belong to this PO.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
where  po_no = 415 and complete <> 'Y' and derivedtbl_1.RN = 1

You're only looking for the first row . . . hence the bolded part in the where clause.  Please try executing it.
0
 
edwardqAuthor Commented:
please bear with me... This is the 1st time I've seen row_number().  

I got it to work with the small query..  I had to correct a understandable few mistakes in the provided code.  (ex. add comma before the row_number,  and remove the extra LEFT OUTER JOIN).  

  I am now working on integrating and testing it in the larger query.    I had to add (derivedtbl_1.RN = 1 or derivedtbl_1.RN IS NULL) to get it to pull all data.
0
 
edwardqAuthor Commented:
Thanks.  They both seem to work.   I'll try both of them.   Scott's does seem easier to understand for me.

Here is the Execution plan for the 2 different ways on the above queries.   I'm not good at reading the execution plans.
Top one is the OUTER Apply,  The bottom one is the row_number() one.
Capture.PNG
0
 
Scott PletcherSenior DBACommented:
Can't really tell from the query plan alone on this one, you'd have to test it.

However, having heaps instead of a clustered index definitely adds to the overhead of the lookup.  But, since the po_line_schedule seems to be keyed on a uniqueidentifier (uid?), uids don't make ideal clustering keys, so you have a tough choice.

You probably can't get people to go along with including the po_no column in the po_line_schedule and using po_no as the first clustering column, but that's likely the best option anyway.  Then change the joins to include the po_no and the uid.  You'd leave the nonclustered index on the uid alone so that current joins continue to work and the uid can be quickly looked up by itself, but joins from the po_line table would be vastly more efficient in every case with po_no included as a leading key.
0
 
edwardqAuthor Commented:
Thanks..   I would not be able to change the database.

Looks like I'm using Outer Apply.  It is easier for me to understand.  But getting points for both since they both work equally.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.