SQLTeam.com | Weblogs | Forums

New to SQL and needing help with Subquery or HAVING clause


#1

Hello,

I've created a query in which I've pulled all the data that I'm looking for, now I'd like to get the most current date for each part number (om.Part_Number)

SELECT DISTINCT 
om.Part_Number AS Part_Number, 
vq.PO_Date AS PO_Date, 
vq.Unit_Cost AS Unit_Cost

FROM dbo.Order_Material om
LEFT OUTER JOIN dbo.vQV_Part_Purchasing vq ON om.PO_Number = vq.PO_Number
WHERE  om.Part_Number  IN  ( '6051005' , '6051013' ) AND om.PO_Number  IS NOT  NULL AND 
vq.Unit_Cost  IS NOT  NULL 
ORDER BY om.Part_Number ASC, vq.PO_Date DESC

That returns data like this:

Part_Number | PO_Date | Unit_Cost
12345 | 02/25/2018 | 2.35
12346 | 02/25/2018 | 2.40
12346 | 03/02/2019 | 2.45
12346 | 03/01/2019 | 2.50

The results I'm looking for should be:

Part_Number | PO_Date | Unit_Cost
12345 | 02/25/2018 | 2.35
12346 | 03/02/2019 | 2.45

I've tried a couple of attempts at a subquery but I don't get the results I'm looking for.

This returns no records:

SELECT DISTINCT 
om.Part_Number AS Part_Number, 
vq.PO_Date AS PO_Date, 
vq.Unit_Cost AS Unit_Cost

FROM dbo.Order_Material om
LEFT OUTER JOIN dbo.vQV_Part_Purchasing vq ON om.PO_Number = vq.PO_Number
WHERE  om.Part_Number  IN  ( '6051005' , '6051013' ) AND om.PO_Number  IS NOT  NULL AND 
vq.Unit_Cost  IS NOT  NULL AND vq.PO_Date = (SELECT MAX(vq.PO_Date)  
FROM dbo.Order_Material om
LEFT OUTER JOIN dbo.vQV_Part_Purchasing vq ON om.PO_Number = vq.PO_Number)

Would a HAVING clause work better than a sub-query. I tried to write one but kept getting errors.

Any help would be appreciated.


#2

please explain the reason those are the records you are wanting as the final result?


#3

SELECT Part_Number,
PO_Date,
Unit_Cost
FROM
(
SELECT om.Part_Number AS Part_Number,
vq.PO_Date AS PO_Date,
vq.Unit_Cost AS Unit_Cost,
ROW_NUMBER() OVER (PARTITION BY om.Part_Number ORDER BY vq.PO_Date) AS RN
FROM dbo.Order_Material om
LEFT OUTER JOIN dbo.vQV_Part_Purchasing vq
ON om.PO_Number = vq.PO_Number
WHERE om.Part_Number IN ( '6051005', '6051013' )
AND om.PO_Number IS NOT NULL
AND vq.Unit_Cost IS NOT NULL
) Z
WHERE RN = 1;

Are you sure it should be a LEFT JOIN and not an INNER JOIN


#4

Lewie,

That worked great, I had to add:

ORDER BY vq.PO_Date DESC)

to get the newest record. Thank you so much for the help.

If you have time can you discuss this line:

ROW_NUMBER() OVER (PARTITION BY om.Part_Number ORDER BY vq.PO_Date) AS RN

I'm not familiar with ROW_NUMBER() or PARTITION BY

Thanks again for your help,


#5

The ROW_NUMBER() will return the row number for each row.
The PARTITION BY part starts the rownumber from 1 for each om.Part_Number.
However, what order are the rows in for each om.Part_Number? Thus the ORDER BY vq.PO_Date