quote:

---

Originally posted by StacyOW

quote:

---

Originally posted by visakh16

can you try providing some sample data from your tables and try explaining the output you want from them?


I'm not exactly sure how to provide you with sample data.

I have a form on which the user enters the date, a vendorID, etc. which is in the porders table. I have an items table linked one to many to the porders table and the items table has all of the items that are ordered for each porder. From the items table when the user enters a part# and then leaves that field I wanted to run a query to bring up the last time (Max Date) that part# was ordered from that Vendor. I can get the query to run but I brings up every time that part# was ordered from the vendor not just one record/the LAST time it was ordered. I have attached an image of the table data my query is returning at this point, so you can see that it is returning 11 records. I just can't seem to get it to do the max date part of the query. Perhaps there is some other way to do a query like this but in my old database program I used MaxDate so I just thought that I would use that in SQL server also.

Thanks for all your help.
Stacy
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by StacyOW

quote:

---

Originally posted by visakh16

can you try providing some sample data from your tables and try explaining the output you want from them?


I'm not exactly sure how to provide you with sample data.

I have a form on which the user enters the date, a vendorID, etc. which is in the porders table. I have an items table linked one to many to the porders table and the items table has all of the items that are ordered for each porder. From the items table when the user enters a part# and then leaves that field I wanted to run a query to bring up the last time (Max Date) that part# was ordered from that Vendor. I can get the query to run but I brings up every time that part# was ordered from the vendor not just one record/the LAST time it was ordered. I have attached an image of the table data my query is returning at this point, so you can see that it is returning 11 records. I just can't seem to get it to do the max date part of the query. Perhaps there is some other way to do a query like this but in my old database program I used MaxDate so I just thought that I would use that in SQL server also.

Thanks for all your help.
Stacy
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

---

see format to give sample data here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

SELECT     vendors.VendorName, items.ItemDescription, items.PartNum, items.ComponentNum, items.UnitPrice
FROM         porders INNER JOIN
                      vendors ON porders.VendorID = vendors.VendorID INNER JOIN
                      items ON porders.PONum = items.PONum
WHERE     (porders.Date =
                          (SELECT     MAX(Date) AS Expr1
                            FROM          porders AS porders_1
                            WHERE      (VendorID = @zVendor1))) AND (items.PartNum LIKE '%' + @zPart + '%')

SELECT VendorName,
       ItemDescription,
       PartNum,
       ComponentNum,
       UnitPrice
FROM   (SELECT vendors.VendorName,
               items.ItemDescription,
               items.PartNum,
               items.ComponentNum,
               items.UnitPrice,
               Row_number()
                 OVER(
                   partition BY porders.VendorID, items.PartNum
                   ORDER BY porders.Date DESC ) rn
        FROM   porders
               INNER JOIN vendors
                       ON porders.VendorID = vendors.VendorID
               INNER JOIN items
                       ON porders.PONum = items.PONum
        WHERE  porders.VendorID = @zVendors
               AND items.PartNum LIKE '%' + @zPart + '%') t
WHERE  rn = 1