Group By, Max and Min

[Aino]

I am struggling with a query, and need help

I have (from a previous saved query) these fields:
Field1, Field2, Field3, Time, Info

What I want to return is the maximum and minimum time along with the
corresponding infos, grouped by the first 3 fields:
Field1, Field2, Field3, Min(Time), Max(Time), InfoForMinTime,
InforForMaxTime

Sofar I have
SELECT T1.Field1, T1.Field2, T1.Field3, T1.Info, Min, Max
FROM [Query] AS T1 INNER JOIN
(SELECT Field1, Field2, Field3, Max(Time) AS Max, Min(Time) AS Min
FROM [Query]
GROUP BY Field1, Field2, Field3) as T2
ON T1.Field1= T2.Field1 AND T1.Field2=T2.Field2 AND
T1.Field3=T2.Field3 AND (T1.Time = T2.Max OR T1.Time=T2.Min)
ORDER BY Field1, Field2, Field3;

Obviously, I only get one Info field from this, and twice as many
records as I want, identical 2 and 2 apart from the Info field.

 

[Michel Walsh]

What happen if info for the record with the min value differs from info from
the record for the max? which info value will we keep? If info is the same
for all records GIVEN field1, field2 and field3, then use:

SELECT field1, field2, field3, LAST(info), MIN(time), MAX(time)
FROM query
GROUP BY field1, field2, field3
ORDER BY field1, field2, field3


If 'info' is not the same for all record, you are likely having two
different values (one associated to min, one to max) and that is why your
actual design cannot be further reduced (in number of rows), unless you
carry info_min and info_max, two info values, in the final result, as two
fields (un-normalized result look ).




Hoping it may help,
Vanderghast, Access MVP

 

[Aino]

Info is not the same, and I want both info.

Anyway, this is the SQL I ended up with. I don't know, if it could
have been done better:

SELECT DISTINCT TMin.Field1, TMin.Field2, TMin.Field3, TMin.TimeMin,
TMin.InfoMin, TMax.TimeMax, TMax.InfoMax
FROM
(SELECT T1.Field1, T1.Field2, T1.Field3, T1.Info AS InfoMin,
TimeMin
FROM [Query] AS T1 INNER JOIN
(SELECT DISTINCT Field1, Field2, Field3, Min(Time) AS TimeMin
FROM [Query]
GROUP BY Field1, Field2, Field3) AS T2
ON (T1.Field1= T2.Field1) AND (T1.Field2=T2.Field2) AND
(T1.Field3=T2.Field3) AND (T1.Time=T2.TimeMin)) AS TMin,
(SELECT T3.Field1, T3.Field2, T3.Field3, T3.Info AS InfoMax,
TimeMax
FROM [Query] AS T3 INNER JOIN
(SELECT DISTINCT Field1, Field2, Field3, Max(Time) AS TimeMax
FROM [Query]
GROUP BY Field1, Field2, Field3) AS T4
ON (T3.Field1= T4.Field1) AND (T3.Field2=T4.Field2) AND
(T3.Field3=T4.Field3) AND (T3.Time=T4.TimeMax)) AS TMax
WHERE TMin.Field1 =TMax.Field1 AND TMin.Field2=TMax.Field2 AND
TMin.Field3=TMax.Field3
ORDER BY T1.Field1, T1.Field2, T1.Field3;

(The actual field names are in danish, hence the none-descript names
used here).

This gets me the result I want. If there is a "better" query for this,
please let me know.

Best regards
Aino