SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
                      items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM         items INNER JOIN
                      porders ON items.PONum = porders.PONum INNER JOIN
                      vendors ON porders.VendorID = vendors.VendorID
WHERE     (items.PartNum = '2122K256') AND (porders.Date =
                          (SELECT     MAX(Date) AS Expr1
                            FROM          porders AS lookup
                            WHERE      (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster - Carr Supply')

WHERE  (items.PartNum = '2122K256')
       AND (
               porders.Date =(
                   SELECT MAX(Date)  AS Expr1
                   FROM   porders    AS lookup
                   WHERE  (Date = porders.Date)
                   AND lookup.PONum = porders.PONum
               )
           )
       AND (vendors.VendorName LIKE 'McMaster - Carr Supply')

quote:

---

Originally posted by sunitabeck

Try adding another condition to the where clause of the inner query as shown below. However, the logic is not entirely clear to me. Can one Partnum+VendorName combination have more than one PONum on the same date?

WHERE  (items.PartNum = '2122K256')
       AND (
               porders.Date =(
                   SELECT MAX(Date)  AS Expr1
                   FROM   porders    AS lookup
                   WHERE  (Date = porders.Date)
                   AND lookup.PONum = porders.PONum
               )
           )
       AND (vendors.VendorName LIKE 'McMaster - Carr Supply')

---

quote:

---

Originally posted by StacyOW

I get an error adding in the red code from above. Error - The multi-part identifier "lookup-PONum" could not be bound.

---

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
                      items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM         items INNER JOIN
                      porders ON items.PONum = porders.PONum INNER JOIN
                      vendors ON porders.VendorID = vendors.VendorID
WHERE     (items.PartNum = '2122K256') AND (porders.Date =
                          (SELECT     MAX(Date) AS Expr1
                            FROM          porders AS lookup
                            WHERE      (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum

quote:

---

Originally posted by StacyOW

I entered it as lookup.PONum and I still get the error. This is what I have right now.

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
                      items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM         items INNER JOIN
                      porders ON items.PONum = porders.PONum INNER JOIN
                      vendors ON porders.VendorID = vendors.VendorID
WHERE     (items.PartNum = '2122K256') AND (porders.Date =
                          (SELECT     MAX(Date) AS Expr1
                            FROM          porders AS lookup
                            WHERE      (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum
))

---

quote:

---

Originally posted by sunitabeck

Does the table porders have a column named PONum?

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by StacyOW

I entered it as lookup.PONum and I still get the error. This is what I have right now.

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
                      items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM         items INNER JOIN
                      porders ON items.PONum = porders.PONum INNER JOIN
                      vendors ON porders.VendorID = vendors.VendorID
WHERE     (items.PartNum = '2122K256') AND (porders.Date =
                          (SELECT     MAX(Date) AS Expr1
                            FROM          porders AS lookup
                            WHERE      (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster') AND lookup.PONum = porders.PONum
))

---

is this the full query?

if yes, you're missing some braces

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

---

SELECT     items.EquipmentID, items.DepartmentID, items.Type, items.ComponentNum, items.PartNum, items.ItemDescription, items.QtyOrd, items.QtyRcvd, items.UnitPrice, 
                      items.ItemUpdate, items.Price, items.Copy, items.WorkNum, items.UpdateOrdered, items.Modified, porders.Date, items.ItemID, items.PONum
FROM         items INNER JOIN
                      porders ON items.PONum = porders.PONum INNER JOIN
                      vendors ON porders.VendorID = vendors.VendorID
WHERE     (items.PartNum = '2122K256') AND (porders.Date =
                          (SELECT     MAX(Date) AS Expr1
                            FROM          porders AS lookup
                            WHERE      (Date = porders.Date))) AND (vendors.VendorName LIKE 'McMaster')

SELECT     
    items.EquipmentID, 
    items.DepartmentID, 
    items.Type, 
    items.ComponentNum, 
    items.PartNum, 
    items.ItemDescription, 
    items.QtyOrd, 
    items.QtyRcvd, 
    items.UnitPrice, 
    items.ItemUpdate, 
    items.Price, 
    items.Copy, 
    items.WorkNum, 
    items.UpdateOrdered, 
    items.Modified, 
    porders.[Date], 
    items.ItemID, 
    items.PONum
FROM         
    items 
INNER JOIN
    porders 
    ON items.PONum = porders.PONum 
INNER JOIN
    vendors 
    ON porders.VendorID = vendors.VendorID

INNER JOIN
    (
        SELECT
            PONum,
            MAX([Date]) AS MaxOrderDate
        FROM          
            porders
        GROUP BY
            PONum
     ) AS MaxOrder
     ON porders.PONum = MaxOrder.PONum
     AND porders.[Date] = MaxOrder.MaxOrderDate
WHERE     
    items.PartNum = '2122K256'
    AND vendors.VendorName LIKE 'McMaster - Carr Supply'

select items.EquipmentID
      ,items.DepartmentID
      ,items.Type
      ,items.ComponentNum
      ,items.PartNum
      ,items.ItemDescription
      ,items.QtyOrd
      ,items.QtyRcvd
      ,items.UnitPrice
      ,items.ItemUpdate
      ,items.Price
      ,items.Copy
      ,items.WorkNum
      ,items.UpdateOrdered
      ,items.Modified
      ,porders.Date
      ,items.ItemID
      ,items.PONum
  from items
       inner join (select PONum
                         ,max(Date) as Date
                     from porders
                    group by PONum
                  ) as porders
               on porders.PONum=items.PONum
       inner join vendors
               on vendors.VendorID=porders.VendorID
 where items.PartNum='2122K256'
   and vendors.VendorName like 'McMaster'

select items.EquipmentID
      ,items.DepartmentID
      ,items.Type
      ,items.ComponentNum
      ,items.PartNum
      ,items.ItemDescription
      ,items.QtyOrd
      ,items.QtyRcvd
      ,items.UnitPrice
      ,items.ItemUpdate
      ,items.Price
      ,items.Copy
      ,items.WorkNum
      ,items.UpdateOrdered
      ,items.Modified
      ,porders.Date
      ,items.ItemID
      ,items.PONum
  from items
       inner join (select row_number() over(partition PONum order by Date desc) as rownum
                         ,*
                     from porders
                  ) as porders
               on porders.PONum=items.PONum
              and porders.rownum=1
       inner join vendors
               on vendors.VendorID=porders.VendorID
 where items.PartNum='2122K256'
   and vendors.VendorName like 'McMaster'

quote:

---

I have three tables. porders, items, and vendors joined together. Porders and Items have a one to many relationship. Vendors is linked to porders by VendorID just so I can query by the VendorName instead of VendorID.

I am trying to do a select query but I want to pull the records (from the porders table) that match my variables for PartNum & VendorName but I only want to pull up the record that has the highest Date. By the porders.Date field. So basically it should only be returning one record. Mine is returning 10 records. The query I was showing in my original post was returning all of the records for McMaster with my variable Part#. I only want to get the last time it was ordered or the highest date!

---

quote:

---

Originally posted by bitsmed

@Lamprey: You are right - I totally missed that.
@StacyOW: Forget my suggestion and go with Lamprey's suggestion

Edit: I really really need to learn the over() function. I think this might work, but my experience with over(), is very very limited

select items.EquipmentID
      ,items.DepartmentID
      ,items.Type
      ,items.ComponentNum
      ,items.PartNum
      ,items.ItemDescription
      ,items.QtyOrd
      ,items.QtyRcvd
      ,items.UnitPrice
      ,items.ItemUpdate
      ,items.Price
      ,items.Copy
      ,items.WorkNum
      ,items.UpdateOrdered
      ,items.Modified
      ,porders.Date
      ,items.ItemID
      ,items.PONum
  from items
       inner join (select row_number() over(partition PONum order by Date desc) as rownum
                         ,*
                     from porders
                  ) as porders
               on porders.PONum=items.PONum
              and porders.rownum=1
       inner join vendors
               on vendors.VendorID=porders.VendorID
 where items.PartNum='2122K256'
   and vendors.VendorName like 'McMaster'

---

over(partition BY PONum order by Date desc)

quote:

---

Originally posted by Lamprey

I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword:

over(partition BY PONum order by Date desc)

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

---

PONum	int	Unchecked - Key - auto-increment
VendorID	int	Checked
OriginatorID	int	Checked
StatusID	int	Checked
ShippedID	int	Checked
Date	date	Unchecked
MPONum	varchar(10)	Checked
DateRequired	date	Checked
RefPONum	real	Checked
ShipTo	varchar(30)	Checked
ShipAddress	varchar(25)	Checked
ShipCity	varchar(15)	Checked
ShipPhone	varchar(12)	Checked
ShipState	varchar(2)	Checked
ShipZip	varchar(10)	Checked
Tax	decimal(18, 2)	Checked
Freight	decimal(18, 2)	Checked
PrintStatus	varchar(3)	Checked
POTotal	money	Checked
Comments	varchar(250)	Checked
Description	varchar(250)	Checked
Modified	timestamp	Unchecked

ItemID	int	Unchecked - key - auto-increment
PONum	int	Unchecked
EquipmentID	int	Unchecked
DepartmentID	int	Unchecked
Type	varchar(1)	Checked
ComponentNum	int	Checked
PartNum	varchar(50)	Checked
ItemDescription	varchar(150)	Checked
QtyOrd	real	Checked
QtyRcvd	real	Checked
UnitPrice	real	Checked
ItemUpdate	varchar(3)	Checked
Price	real	Checked
Copy	varchar(3)	Checked
WorkNum	varchar(12)	Checked
UpdateOrdered	varchar(3)	Checked
Modified	timestamp	Unchecked

VendorID	int	Unchecked - Key - Auto-increment
VendorName	varchar(50)	Unchecked
Address	varchar(50)	Checked
Address2	varchar(50)	Checked
City	varchar(50)	Checked
State	varchar(50)	Checked
Zip	varchar(10)	Checked
Phone	varchar(50)	Checked
Fax	varchar(50)	Checked
VendorType	varchar(50)	Checked
ProductsServices	varchar(250)	Checked
Comments	varchar(250)	Checked
MinOrdAmnt	varchar(25)	Checked
WebAddress	varchar(50)	Checked
Modified	timestamp	Unchecked

quote:

---

Originally posted by Lamprey

I don't know for certian if this is the only issue as you have not provided any DDL or DML, but there is a small syntax error on the OVER clause. The PARTITION needs the BY keyword:

over(partition BY PONum order by Date desc)

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

---

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.

Sorry I can't seem to find a way to add an image from my computer to this post.

Thanks for all your help.
Stacy
------------------------------------------------------------------------------------------------------

---