query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query problem

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2008-07-20 : 19:06:22

I have a vendor table, product table, and productpurchasing table.
I want a list of the most recent date of sale, vendor name, etc. of all vendors that have sold product (x)

so:
vendor 2 prod 6 7/8/2008
vendor 2 prod 6 1/1/2008
vendor 1 prod 6 7/28/2008

would list:
vendor 2 prod 6 7/8/2008
vendor 1 prod 6 7/28/2008

My query only gets the vendor with the most recent dates sold of all vendors selling that product. I want the most recent date sold of every vendor who has sold that product.

SELECT tblVendor.VendorCode,
tblVendor.VendorName,
tblVendor.IsActive,
tblProductPurchase.ProductPurchasingID, tblProductPurchase.ProductID, tblProductPurchase.VendorID,
tblProductPurchase.PurchaseDate
FROM tblProduct
INNER JOIN
tblProductPurchase ON tblProduct.ProductID = tblProductPurchase.ProductID
INNER JOIN
tblVendor ON tblProductPurchase.VendorID = tblVendor.VendorID
where tblProductPurchase.PurchaseDate=(select max(PurchaseDate) from tblProductPurchase) and tblProduct.ProductID = 6

I also tried a group by but received an error:

SELECT tblVendor.VendorCode, tblProductPurchase.ProductID,
tblProductPurchase.VendorID, max(tblProductPurchase.PurchaseDate)
FROM tblProduct
INNER JOIN
tblProductPurchase ON tblProduct.ProductID = tblProductPurchase.ProductID
INNER JOIN
tblVendor ON tblProductPurchase.VendorID = tblVendor.VendorID
where tblProduct.ProductID = 6
group by max(tblProductPurchase.PurchaseDate), tblProductPurchase.VendorID, tblVendor.VendorCode

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 19:20:48

[code]SELECT TOP 1 WITH TIES
tblVendor.VendorCode,
tblVendor.VendorName,
tblVendor.IsActive,
tblProductPurchase.ProductPurchasingID,
tblProductPurchase.ProductID,
tblProductPurchase.VendorID,
tblProductPurchase.PurchaseDate
FROM tblProduct
INNER JOIN tblProductPurchase ON tblProductPurchase.ProductID = tblProduct.ProductID
INNER JOIN tblVendor ON tblVendor.VendorID = tblProductPurchase.VendorID
WHERE tblProduct.ProductID = 6
ORDER BY tblProductPurchase.PurchaseDate DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

smh
Yak Posting Veteran

94 Posts

Posted - 2008-07-20 : 19:30:02

I am afraid that this gave me only the record with the most recent date like the select sub query sample I mentioned.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 19:34:18

[code]SELECT VendorCode,
VendorName,
IsActive,
ProductPurchasingID,
ProductID,
VendorID,
PurchaseDate
FROM (
SELECT tblVendor.VendorCode,
tblVendor.VendorName,
tblVendor.IsActive,
tblProductPurchase.ProductPurchasingID,
tblProductPurchase.ProductID,
tblProductPurchase.VendorID,
tblProductPurchase.PurchaseDate,
ROW_NUMBER() OVER (PARTITION BY tblProductPurchase.VendorID ORDER BY tblProductPurchase.PurchaseDate DESC) AS RecID
FROM tblProduct
INNER JOIN tblProductPurchase ON tblProductPurchase.ProductID = tblProduct.ProductID
INNER JOIN tblVendor ON tblVendor.VendorID = tblProductPurchase.VendorID
WHERE tblProduct.ProductID = 6
) AS d
WHERE RecID = 1[/code]

EDIT: Change place of WHERE clause.

E 12°55'05.25"
N 56°04'39.16"

smh
Yak Posting Veteran

94 Posts

Posted - 2008-07-20 : 19:41:57

I am afraid I get "Incorrect syntax near the keyword 'INNER'"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 19:44:32

See edited response above.

E 12°55'05.25"
N 56°04'39.16"

smh
Yak Posting Veteran

94 Posts

Posted - 2008-07-20 : 19:52:38

Thank you so much. I wonder if you might explain your approach -- or suggest somewhere to find more on the approach. I see an outer and inner select. I am not sure how the inner select works -- specifically:
ROW_NUMBER() OVER (PARTITION BY tblProductPurchase.VendorID ORDER BY tblProductPurchase.PurchaseDate DESC) AS RecID
and I am not sure how the "where recID = 1" works in the outer statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-20 : 19:53:35

There are only INNER SELECT.
I've used a derived table.

You can read all about ROW_NUMBER() and other windowed functions in Books Online.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources