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
EdwardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
EdwardAuthor 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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EdwardAuthor 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
EdwardAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.