Max(ofDate) does not pull max?

[MrsMope]

Here is my query:

SELECT tblPart_Manufacturer.ID, dbo_Part_ID.Description, tblOrder.Units_Ordered, Max(tblOrder.Date_Ordered) AS MaxOfDate_Ordered, tblOrder.Expected_Delivery_Date, tblOrder.Date_Received, tblOrder.OrderReceived, tblOrder.PurchaseOrderNumber
FROM dbo_Part_ID INNER JOIN (tblPart_Manufacturer INNER JOIN tblOrder ON tblPart_Manufacturer.ID = tblOrder.Part_Manufactur_ID) ON dbo_Part_ID.Part_ID = tblPart_Manufacturer.Part_ID
GROUP BY tblPart_Manufacturer.ID, dbo_Part_ID.Description, tblOrder.Units_Ordered, tblOrder.Expected_Delivery_Date, tblOrder.Date_Received, tblOrder.OrderReceived, tblOrder.PurchaseOrderNumber;

I still get two records:

ID	Description	Units_Ordered	MaxOfDate_Ordered	Expected_Delivery_Date	Date_Received	OrderReceived	PurchaseOrderNumber
1	C30-060410	5	1/15/2005	1/20/2005		No	0
1	C30-060410	10	1/10/2005	1/15/2005		No	0

Any ideas?

 

[lespaul]

This should work for what you are looking for. (I hope, I've done this myself, but it's much harder modifing a query that you're not really sure of!!)

HTH

Leslie

SELECT P.ID, P.Description, O.Units_Ordered, J.MaxOfDate_Ordered, O.Expected_Delivery_Date, O.Date_Received, O.OrderReceived, O.PurchaseOrderNumber
    FROM Orders O
    INNER JOIN 
      (SELECT Part_Manufactur_ID, Max(tblOrder.Date_Ordered) AS MaxOfDate_Ordered 
         FROM Orders) 
     As J ON O.Part_Manufactur_ID = J.Part_Manufactur_ID
     INNER JOIN dbo_Part_ID P ON O.Part_Manufactur_ID = P.Part_ID

Leslie

 

[Golom]

What are you trying to get?

Your SQL is returning two records because you will get one record for each unique concatenation of the fields in your GROUP BY clause. Because "Units_Ordered" and "Expected_Delivery_Date" are different, you get different records.

Leslie's solution will give you one record for each Part_ID. Is that what you want?

 

[MrsMope]

Yes, I want one record for each Part_ID here is what I did:
[Code}

SELECT M.ID,
P.description,
O.Units_Ordered,
O.Date_Ordered,
O.Expected_Delivery_Date,
O.Date_Received,
O.OrderReceived,
O.PurchaseOrderNumber
FROM tblPart_Manufacturer M,
tblOrder O,
dbo_Part_ID P
WHERE O.Part_Manufactur_ID= M.ID
and M.Part_ID= P.Part_ID and
O.Date_Ordered= (SELECT Max(Date_Ordered) from tblOrder
WHERE tblOrder.Part_Manufactur_ID=tblPart_Manufacturer.ID )
and M.ID= 505
GROUP BY M.ID,
P.Description,
O.Units_Ordered,
O.Date_Ordered,
O.Expected_Delivery_Date,
O.Date_Received,
O.OrderReceived,
O.PurchaseOrderNumber [/code]

 

[lespaul]

by adding the subquery:

O.Date_Ordered= (SELECT Max(Date_Ordered) from tblOrder 
            WHERE tblOrder.Part_Manufactur_ID=tblPart_Manufacturer.ID )

you are accomplishing the same thing I did when I joined into the select statement. Depending on your table size this may not make a difference, but if you have a large recordset, I think your solution could run very slowly.

You are also using a cartesian product (FROM A, B, C) and while your results may look correct with a small sample of data, you may experience weird results with a larger sample set.






Leslie

 

[Golom]

Should work. The GROUP BY is not necessary because you will get only one record retrieved based on the constraints in the WHERE clause and you are not using any aggregate functions in the SELECT clause.

 

[MrsMope]

thank you to you both, I have changed my query to match the one that Leslie posted.