I am obviously not understanding something very basic with regard to Group BY 
and Max() sql statements.

All I want is to return the entry which has the greatest date value per unit 
no.  I have the following

SELECT unit_no, Max(eta_date) AS MaxOfeta_date, tbl_cngu_qt.eta_city
FROM tbl_qt
GROUP BY cngu_no, eta_city
ORDER BY cngu_no;

but it returns multiple entries per unit??

What is the proper synthax for this query?

QB

I came up with the following.  It seems to work, but would still like to know 
if a) this is proper, b) if there is a more efficient method

SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
FROM [
SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date 
FROM tbl_qt GROUP BY tbl_qt.unit_no ORDER BY tbl_qt.unit_no 
]. AS Temp1 
LEFT JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND 
(Temp1.cngu_no = tbl_qt.unit_no);

QB



"QB" wrote:

> I am obviously not understanding something very basic with regard to Group BY 
> and Max() sql statements.
> 
> All I want is to return the entry which has the greatest date value per unit 
> no.  I have the following
> 
> SELECT unit_no, Max(eta_date) AS MaxOfeta_date, tbl_cngu_qt.eta_city
> FROM tbl_qt
> GROUP BY unit_no, eta_city
> ORDER BY unit_no;
> 
> but it returns multiple entries per unit??
> 
> What is the proper synthax for this query?
> 
> QB

QB,

If what you are trying to do is get the unit #, and the 1 city associated 
with the Maximum (ETA_Date) for each unit, then your tecnique below would 
work (although I would move the Order By clause out of the subquery and put 
it in the outer part of the query.  Another method to do this is:

SELECT Unit_No, Eta_Date, Eta_City
FROM tbl_qt
WHERE eta_Date = (SELECT Max(Eta_Date)
                                 FROM tbl_qt as T
                                 WHERE T.Unit_No = tbl_Qt.Unit_No)

Personally, I prefer the method you used, but its always good to know your 
alternatives.

HTH
Dale


"QB" <QB@discussions.microsoft.com> wrote in message 
news:319993B8-04DE-4F5F-BFC2-D24A1E75ACF5@microsoft.com...
>I came up with the following.  It seems to work, but would still like to 
>know
> if a) this is proper, b) if there is a more efficient method
>
> SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
> FROM [
> SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date
> FROM tbl_qt GROUP BY tbl_qt.unit_no ORDER BY tbl_qt.unit_no
> ]. AS Temp1
> LEFT JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND
> (Temp1.cngu_no = tbl_qt.unit_no);
>
> QB
>
>
>
> "QB" wrote:
>
>> I am obviously not understanding something very basic with regard to 
>> Group BY
>> and Max() sql statements.
>>
>> All I want is to return the entry which has the greatest date value per 
>> unit
>> no.  I have the following
>>
>> SELECT unit_no, Max(eta_date) AS MaxOfeta_date, tbl_cngu_qt.eta_city
>> FROM tbl_qt
>> GROUP BY unit_no, eta_city
>> ORDER BY unit_no;
>>
>> but it returns multiple entries per unit??
>>
>> What is the proper synthax for this query?
>>
>> QB 

Dale Fye's method is a good one if you need to be able to edit the records in 
the query or if you have NOT followed the naming guidelines for tables and 
fields (Letters, Numbers, and underscore characters only and not RESERVED 
words such as Date).

Your original method is generally faster but I believe you will find that you 
cannot edit the records returned by this query.

One additional option is to use nested queries if your field and table names 
do not follow the naming guidelines.

Save a query as q1
SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date
FROM tbl_qt
GROUP BY tbl_qt.unit_no

Now use that query in your subsequent query
SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
FROM Q1 AS Temp1
LEFT JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND
(Temp1.cngu_no = tbl_qt.unit_no);

In your existing query you might drop the order by clause in the subquery as 
Dale noted.  Although it will probably be ignored since it is not needed for 
the execution of the subquery.

And I would change the LEFT JOIN to an INNER JOIN.  I don't KNOW that an INNER 
JOIN is more efficient, but I SUSPECT that it might be and I can see no reason 
not to use an INNER JOIN since there has to be a matching record.

SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
FROM [
SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date
FROM tbl_qt GROUP BY tbl_qt.unit_no
]. AS Temp1
INNER JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND
(Temp1.cngu_no = tbl_qt.unit_no)

QB
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dale Fye wrote:
> QB,
> 
> If what you are trying to do is get the unit #, and the 1 city associated 
> with the Maximum (ETA_Date) for each unit, then your tecnique below would 
> work (although I would move the Order By clause out of the subquery and put 
> it in the outer part of the query.  Another method to do this is:
> 
> SELECT Unit_No, Eta_Date, Eta_City
> FROM tbl_qt
> WHERE eta_Date = (SELECT Max(Eta_Date)
>                                  FROM tbl_qt as T
>                                  WHERE T.Unit_No = tbl_Qt.Unit_No)
> 
> Personally, I prefer the method you used, but its always good to know your 
> alternatives.
> 
> HTH
> Dale
> 
> 
> "QB" <QB@discussions.microsoft.com> wrote in message 
> news:319993B8-04DE-4F5F-BFC2-D24A1E75ACF5@microsoft.com...
>> I came up with the following.  It seems to work, but would still like to 
>> know
>> if a) this is proper, b) if there is a more efficient method
>>
>> SELECT Temp1.unit_no, Temp1.MaxOfeta_date AS eta_date, tbl_qt.eta_city
>> FROM [
>> SELECT tbl_qt.unit_no, Max(tbl_qt.eta_date) AS MaxOfeta_date
>> FROM tbl_qt GROUP BY tbl_qt.unit_no ORDER BY tbl_qt.unit_no
>> ]. AS Temp1
>> LEFT JOIN tbl_qt ON (Temp1.MaxOfeta_date = tbl_qt.eta_date) AND
>> (Temp1.cngu_no = tbl_qt.unit_no);
>>
>> QB
>>
>>
>>
>> "QB" wrote:
>>
>>> I am obviously not understanding something very basic with regard to 
>>> Group BY
>>> and Max() sql statements.
>>>
>>> All I want is to return the entry which has the greatest date value per 
>>> unit
>>> no.  I have the following
>>>
>>> SELECT unit_no, Max(eta_date) AS MaxOfeta_date, tbl_cngu_qt.eta_city
>>> FROM tbl_qt
>>> GROUP BY unit_no, eta_city
>>> ORDER BY unit_no;
>>>
>>> but it returns multiple entries per unit??
>>>
>>> What is the proper synthax for this query?
>>>
>>> QB 
> 
>